Forum Discussion
Running Totals
I know there are a plethora of running total posts but I haven't found an answer to my specific issue. My running totals need to be based on the DateDiff field which is a measure. Running1 is a cumulative sum and resets on the wr_lot number. RunningTotal has no breaks...just continues the cumulative sum. The table name is wr_route. My question is what is the code to get Running1 and RunningTotal.
| wr_nbr | wr_lot | MinDate | MaxDate | DateDiff | Running1 | RunningTotal |
| 317967 | 18315747 | 5/8/2019 | 5/9/2019 | 1 | 0 | 0 |
| 317967 | 18315747 | 5/14/2019 | 5/14/2019 | 0 | 1 | 1 |
| 317967 | 18315747 | 5/15/2019 | 5/15/2019 | 0 | 1 | 1 |
| 317967 | 18315747 | 5/19/2019 | 5/21/2019 | 2 | 3 | 3 |
| 317967 | 18315747 | 5/21/2019 | 5/23/2019 | 2 | 5 | 5 |
| 317968 | 18315748 | 5/9/2019 | 5/9/2019 | 0 | 0 | 5 |
| 317968 | 18315748 | 5/14/2019 | 5/14/2019 | 0 | 0 | 5 |
| 317968 | 18315748 | 5/15/2019 | 5/15/2019 | 0 | 0 | 5 |
| 317968 | 18315748 | 5/16/2019 | 5/20/2019 | 4 | 4 | 9 |
| 317968 | 18315748 | 5/20/2019 | 5/20/2019 | 0 | 0 | 9 |
| 317969 | 18315749 | 5/10/2019 | 5/10/2019 | 0 | 0 | 9 |
| 317969 | 18315749 | 5/14/2019 | 5/14/2019 | 0 | 0 | 9 |
| 317969 | 18315749 | 5/15/2019 | 5/15/2019 | 0 | 0 | 9 |
| 317969 | 18315749 | 5/18/2019 | 5/18/2019 | 0 | 0 | 9 |
| 317969 | 18315749 | 5/22/2019 | 5/23/2019 | 1 | 1 | 10 |
Actually I found another solution that's a little simpler:
Running1 = CALCULATE(SUM(wr_route[DateDiff),FILTER(ALLSELECTED(wr_route),wr_route[wr_lot]=MAX(wr_route[wr_lot]) &&wr_route[MaxDate]<=MAX(wr_route[MaxDate])))
4 Replies
Hi,
This measure works for Running1
Running1 = SUMX(SUMMARIZE(CALCULATETABLE(wr_route,DATESBETWEEN(wr_route[MinDate],MINX(ALL(wr_route[MinDate]),wr_route[MinDate]),MAX(wr_route[MinDate])),DATESBETWEEN(wr_route[MaxDate],MINX(ALL(wr_route[MaxDate]),wr_route[MaxDate]),MAX(wr_route[MaxDate]))),wr_route[MinDate],wr_route[MaxDate],"ABCD",[Datediff]),[ABCD])
Someone else will help you with the second measure
Thanks for the quick response. Something I should have mentioned is that MinDate and MaxDate are measures not columns so the error I get is "Column 'MinDate' in table 'wr_route' cannot be found or may not be used in this expression". The fact that these are measures is my biggest issue. Thanks
You are welcome. You might as well share the link from where i can download your PBI file.
