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

2 REPLIES 2
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 !

## 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 Solution Authors
Top Kudoed Authors