Compare cumulative running total vs previous year cumulative running total for same period

[egarstad]

I have this 95% working but the measure for the previous year needs a tweak and I can't quite figure it out.

This measure is for the running total for the current period and is working as intended. Using the filter pane to select a date range, this measure always calculates the cumulative total starting from 0 at the first date specified in the filter. So if I filter the month to May, the cumulative total starts at 0 on May 1st and increments from there:

Cumulative Booked Sales F =

VAR LastSalesDate = CALCULATE (

MAX ( dimDate[Date] ),

dimDate[DateWithinActualRange] = true

)

RETURN

IF( SELECTEDVALUE( dimDate[Date] ) > LastSalesDate, BLANK(),

CALCULATE( [Total Booked Sales] ,

FILTER( ALLSELECTED( dimDate ),

dimDate[Date] <= MAX( dimDate[Date] ) )))

This measure is for the same period of the previous year, but it is always starting the cumulative total at January 1st. So if I filter the month to May it still calculates the cumulative total as if the first date was January 1st of the previous year..

PYE Cumulative Booked Sales =

CALCULATE ( [Total Booked Sales],

DATESYTD (

DATEADD ( dimDate[Date], -1, YEAR )

)

)

I've tried various methods of altering the previous year measure with no success. Can anyone help me out?

Here is a picture of the chart. I would like the previous year measure to start at 0 just like the measure for the current year.

Thank you!

[v-xicai]

Hi @egarstad   ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai]

Hi @egarstad ,

You may create measure like DAX below.

PYE Cumulative Booked Sales  =  CALCULATE([Total Booked Sales],DATESMTD(ENDOFMONTH(DATEADD('dimDate'[Date],-12,MONTH))))

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tomhayw]

Hi, I've used this in my project, however it isn't showing as a cumulative - it just shows the values for that month last year. It's nearly there it just needs to cumulate up. Any ideas?

[AiolosZhao]

Hi @egarstad ,

Because you don't show your data sample or data model. So I create some sample data by myself.

It may help you.

Measure: 

MEASURE = CALCULATE(SUM(Party[value]),FILTER(ALL(Party[Month]),Party[Month] <= MAX(Party[Month])))

Using "Greater than or equal to" as the filter type.