Skip to main content
cancel
Showing results for 
Search instead 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

Reply
egarstad
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.

 

Example.png

 

Thank you!

 

4 REPLIES 4
v-xicai
Community Support
Community Support

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
Community Support
Community Support

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.

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
Memorable Member
Memorable Member

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.

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.