Cumulative sum of a measure

[ICWiener]

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,

[Greg_Deckler]

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

[ICWiener · Accepted solution]

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 !