cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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!

4 REPLIES 4
Community Support

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

Community Support

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.

Helper I

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?

Memorable Member

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

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.

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.