Cumulative PY differences

DAX DAX Calculations
1

Hi,

I calculated Cumulative Sales and Cumulative Sales PY in 2 different ways, one called Revenue (worked fine) the other one called Sales (not working as expected for PY only).

Cumulative Revenue =
CALCULATE( [Revenue],
FILTER( ALLSELECTED( ‘Calendar’ ),
‘Calendar’[Date] <= MAX( ‘Calendar’[Date] ) ))

Sales PY =
CALCULATE(
[Revenue], DATEADD(‘Calendar’[Date],-1,YEAR))

Cumulative Revenue PY =
CALCULATE( [Sales PY],
FILTER( ALLSELECTED( ‘Calendar’ ),
‘Calendar’[Date] <= MAX( ‘Calendar’[Date] ) ))

Cumulative Sales =
VAR LastSalesDate = CALCULATE( MAX( ‘Calendar’[Date] ), ALL( ‘Calendar’) )

RETURN
IF( SELECTEDVALUE( ‘Calendar’[Date] ) > LastSalesDate, BLANK(),
CALCULATE( [Revenue],
FILTER( ALLSELECTED( ‘Calendar’[Date] ),
‘Calendar’[Date] <= MAX( ‘Calendar’[Date] ))))

Cumulative Sales PY =
CALCULATE(
[Cumulative Sales],
SAMEPERIODLASTYEAR(‘Calendar’[Date])
)

Moreover I which one should I always use over the other from a simplicity and performance standpoint.

Thanks a lot

2

It depends on exactly what you require.

image
074E54EE-2B0B-4925-861C-8F4F433BB178.jpeg2048×1536 514 KB

The first ones are fine and would be simple best practice. The 3rd formula also looks fine if you want that logic.

The last one isn’t recommended. You want to use the top examples instead.

Thanks
Sam

1 Like
3

Thanks a lot, will bear that in mind going forward. This was me testing different code to get the same result whereby SAMEPERIODLASTYEAR didn’t do the job.