Comparing Different Time Periods

https://www.daxpatterns.com/comparing-different-time-periods/

Compare measure amounts using 2 different time periods.

Add a new Date table (e.g. Comparison Date) that is a duplicate of the existing date table. 

The relationship between Date and Comparison Date is Many-to-1 and Inactive. Cross filter direction = Single. Activate the relationship in the measure.

Add 2 slicers; one uses Date table, the other uses Comparison Date table.

Compare measures using the 2 Date tables.

Comparison Sales Amount =

CALCULATE([Sales Amount], REMOVEFILTERS('Date'), 

USERELATIONSHIP('Date'[Date], 'Comparison Date'[Comparison Date])

)




Comments

Post a Comment

Popular posts from this blog

Difference between DISTINCT and VALUES in DAX

https://www.sqlbi.com/blog/marco/2011/03/08/difference-between-distinct-and-values-in-dax/   Example: If a  FACT table contains values that do not exist in a related DIMENSION table, a BLANK row is automatically added to the DIMENSION table. This DIMENSION row with the BLANK value will join to any FACT row where the column value does not exist in the DIMENSION. DISTINCT will NOT include the BLANK column in the DIMENSION. VALUES will include the BLANK column in the DIMENSION.
Read more