cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Cumulative sum of a measure

Hello,
I've been strugling with my problem for 2 weeks now. My background is excel, so i understand calculated columns, but not measures at all. What i'm trying to achieve here is a cumulative sum of a measure. First, let me expose the data : 

 

- 1 table 'History_All' where 1 line = 1 part, and columns are ID / Plant / Engine / Status1 Entry / Status1 Exit / Status2 Entry / Status2 Exit (several status so several columns like the last ones)

- 1 calendar 'Jours' (= 'days' in french) table created from scratch with a column Date = CALENDAR (DATE(2017;1;1); DATE(2018;12;31))

 

I can make everything work using calculated columns, except that when i try to filter by plants or engine (or both) it does not filter, because i've found no way to use FILTERS involving these columns (hence my slicers are not 'connected' to these columns). When using measures, everything filters automatically when adding slicers

 

Goal : 

- Show number of parts per day --> Achieved with the help of the measure [get date]

get date = max(Jours[Date])

Status1 = CALCULATE(counta(History_All[ID]);FILTER(History_All;History_All[Status1.Entry]<=[get date]&&History_All[Status.Exit]>=[get date]))

- Show a prevision of parts in each status, which would be :

for days > today, take the last known value, add the average of added parts (with Status1.Entry) per day in this status

This is what i can't achieve because it would involve a recursive sum based on a measure (and not a calculated column). 

I managed to get the last value : 

Status1 Last = CALCULATE([Status1];Jours[Date]=TODAY())

I managed to get the average of last month :

Added = CALCULATE(counta(History_All[ID]);FILTER(History_All;History_All[Status1.Entry]=[get date]))

Added Average = Calculate(AVERAGEX(Jours;[Added]);DATESINPERIOD(Jours[Date];lastdate(Jours[Date]);-30;DAY))

Added Average last = CALCULATE([Added Average];Jours[Date]=TODAY())

I was planning to make a cumulative value of [Added Average last] and add this to [Status1 last] and that would make my prevision line, but that only works in a 'calculated column' state of mind (SUMX for example, doesn't work with measure, and my problem is that i don't get why, because i can't picture what a measure is exactly.)

 

Any help would be appreciated, even a good link to understand what a measure is exactly.

Regards,

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Cumulative sum of a measure

Hello,

Thanks for the useful link, however i could not apply it in my case. 
But I have figured out how to make my trend line :

 

1. Get the last day with data :

get date Last = CALCULATE([get date];Jours[Date]=TODAY())

2. Extend the data past today : the formula below writes the [Status1] value if it has a value, otherwise it writes the last value (the one from today)

Status1 Extended = if([get date] > [get date Last];CALCULATE([Status1];Jours[Date]=TODAY());[Status1]) 

3. Get the rolling average (30days) of added parts in this status

Status1 added = CALCULATE(counta(History_All[ID]);FILTER(History_All;History_All[Status1.Entry]<=[get date] && History_All[Status1.Exit]>=[get date]))

Status1 added avg = Calculate(AVERAGEX(Jours;[Status1 added]);DATESINPERIOD(Jours[Date];lastdate(Jours[Date]);-30;DAY))

 4. Get the last average and extend it beyond today :

Status1 added avg extended = if([get date] > [get date Last];CALCULATE([Status1 added avg];Jours[Date]=TODAY());blank())

5. Get the cumulative sum of this average (That was the missing measure i could not find) 

EDIT : this formula is based on the method found at this link : https://blog.gbrueckl.at/2015/04/recursive-calculations-powerpivot-dax/

 

Status1 Cumulated Addby = 
	Calculate(
		sumx( values(Jours[Date]); [Status1 added avg extended]);
		datesbetween(
				Jours[Date];blank();max(Jours[Date])
		)
	)

6. Addition between Status1 Extended and Status1 Cumulated Addby gives us the trend :

Status1 Trend = if([get date] >= [get date Last];[Status1 Extended] + [Status1 Cumulated Addby];BLANK())

 

This is a little bit dirty as i'm not DAX expert but it does the trick ! 

View solution in original post

2 REPLIES 2
Highlighted
Super User IX
Super User IX

Re: Cumulative sum of a measure

Check out this article, I am guessing that you might have a similar situation.

 

https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Cumulative sum of a measure

Hello,

Thanks for the useful link, however i could not apply it in my case. 
But I have figured out how to make my trend line :

 

1. Get the last day with data :

get date Last = CALCULATE([get date];Jours[Date]=TODAY())

2. Extend the data past today : the formula below writes the [Status1] value if it has a value, otherwise it writes the last value (the one from today)

Status1 Extended = if([get date] > [get date Last];CALCULATE([Status1];Jours[Date]=TODAY());[Status1]) 

3. Get the rolling average (30days) of added parts in this status

Status1 added = CALCULATE(counta(History_All[ID]);FILTER(History_All;History_All[Status1.Entry]<=[get date] && History_All[Status1.Exit]>=[get date]))

Status1 added avg = Calculate(AVERAGEX(Jours;[Status1 added]);DATESINPERIOD(Jours[Date];lastdate(Jours[Date]);-30;DAY))

 4. Get the last average and extend it beyond today :

Status1 added avg extended = if([get date] > [get date Last];CALCULATE([Status1 added avg];Jours[Date]=TODAY());blank())

5. Get the cumulative sum of this average (That was the missing measure i could not find) 

EDIT : this formula is based on the method found at this link : https://blog.gbrueckl.at/2015/04/recursive-calculations-powerpivot-dax/

 

Status1 Cumulated Addby = 
	Calculate(
		sumx( values(Jours[Date]); [Status1 added avg extended]);
		datesbetween(
				Jours[Date];blank();max(Jours[Date])
		)
	)

6. Addition between Status1 Extended and Status1 Cumulated Addby gives us the trend :

Status1 Trend = if([get date] >= [get date Last];[Status1 Extended] + [Status1 Cumulated Addby];BLANK())

 

This is a little bit dirty as i'm not DAX expert but it does the trick ! 

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors