cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
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

 


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

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

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

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors