Forum Discussion
Cumulative/Running Total
Hi ImkeF
I'm not sure if I can upload a sample file but i'll try this... I was able to make this work in Excel example below
Example:
| Month | ActiveFTE | TermsFTE | EndFTE | Turnover | Running Turnover |
| August | 3 | 1 | 2 | 0.4 | 0.4 |
| July | 3 | 1 | 2 | 0.4 | 0.8 |
| June | 3 | 1 | 2 | 0.4 | 1.2 |
Table1-Active
| EENo | Date | StartFTE |
| 1 | 6/1/2019 | 1 |
| 2 | 6/1/2019 | 1 |
| 3 | 6/1/2019 | 1 |
| 4 | 7/1/2019 | 1 |
| 5 | 7/1/2019 | 1 |
| 6 | 7/1/2019 | 1 |
| 7 | 8/1/2019 | 1 |
| 8 | 8/1/2019 | 1 |
| 9 | 8/1/2019 | 1 |
Table2-Terms
| EENo | Date | TermFTE |
| 3 | 6/4/2019 | 1 |
| 6 | 7/5/2019 | 1 |
| 9 | 8/9/2019 | 1 |
Table3-Date
| Date | Month |
| 6/1/2019 | June |
| 6/2/2019 | June |
| 6/3/2019 | June |
| 6/4/2019 | June |
| 6/5/2019 | June |
| 6/6/2019 | June |
| 6/7/2019 | June |
| 6/8/2019 | June |
| 6/9/2019 | June |
| 6/10/2019 | June |
| 6/11/2019 | June |
| 6/12/2019 | June |
| 6/13/2019 | June |
| 6/14/2019 | June |
| 6/15/2019 | June |
| 6/16/2019 | June |
| 6/17/2019 | June |
| 6/18/2019 | June |
| 6/19/2019 | June |
| 6/20/2019 | June |
| 6/21/2019 | June |
| 6/22/2019 | June |
| 6/23/2019 | June |
| 6/24/2019 | June |
| 6/25/2019 | June |
| 6/26/2019 | June |
| 6/27/2019 | June |
| 6/28/2019 | June |
| 6/29/2019 | June |
| 6/30/2019 | June |
| 7/1/2019 | July |
| 7/2/2019 | July |
| 7/3/2019 | July |
| 7/4/2019 | July |
| 7/5/2019 | July |
| 7/6/2019 | July |
| 7/7/2019 | July |
| 7/8/2019 | July |
| 7/9/2019 | July |
| 7/10/2019 | July |
| 7/11/2019 | July |
| 7/12/2019 | July |
| 7/13/2019 | July |
| 7/14/2019 | July |
| 7/15/2019 | July |
| 7/16/2019 | July |
| 7/17/2019 | July |
| 7/18/2019 | July |
| 7/19/2019 | July |
| 7/20/2019 | July |
| 7/21/2019 | July |
| 7/22/2019 | July |
| 7/23/2019 | July |
| 7/24/2019 | July |
| 7/25/2019 | July |
| 7/26/2019 | July |
| 7/27/2019 | July |
| 7/28/2019 | July |
| 7/29/2019 | July |
| 7/30/2019 | July |
| 7/31/2019 | July |
| 8/1/2019 | August |
| 8/2/2019 | August |
| 8/3/2019 | August |
| 8/4/2019 | August |
| 8/5/2019 | August |
| 8/6/2019 | August |
| 8/7/2019 | August |
| 8/8/2019 | August |
| 8/9/2019 | August |
| 8/10/2019 | August |
| 8/11/2019 | August |
| 8/12/2019 | August |
| 8/13/2019 | August |
| 8/14/2019 | August |
| 8/15/2019 | August |
| 8/16/2019 | August |
| 8/17/2019 | August |
| 8/18/2019 | August |
| 8/19/2019 | August |
| 8/20/2019 | August |
| 8/21/2019 | August |
| 8/22/2019 | August |
| 8/23/2019 | August |
| 8/24/2019 | August |
| 8/25/2019 | August |
| 8/26/2019 | August |
| 8/27/2019 | August |
| 8/28/2019 | August |
| 8/29/2019 | August |
| 8/30/2019 | August |
| 8/31/2019 | August |
jake223,
Give this a try,
Cumulative =
VAR minDate = CALCULATE(MIN('7_Date'[Date]) ; ALLSELECTED('7_Date'))
VAR maxDate = MAX('7_Date'[Date])
Return
SUMX(
SUMMARIZE(
FILTER(ALL('7_Date');
AND('7_Date'[Date] >= minDate ; '7_Date'[Date] <= maxDate)
);
'7_Date'[Year Month];
"Turnover_";
[Turnover]
);
[Turnover_]
)
I gave it a try with an monthly index I had and atleast for me it seems to be producing the correct result. Let me know how it goes.
I think I've stumbled on the issue but not sure how to correct. When I use the Date slider visual and set it to beginning of FY (June 1 2019) and End date (Dec 31 2019) the Cumulative total works.However, when I use the date dropdown visual it does not. It looks like when I use the date dropdown visual and select July the Date Min is 7/1 and Date Max is 7/31. See below both ways that I've tested this.
1st image
2nd Image
My question is without using the date slider visual how do i get the Cumulative Measure to look back at past months from the current month that I select. The first image is displaying the correct information.
Hi jake223
this depends on your measures and data model.
If you've used "ALLSELECTED" somewhere in your measure-chain, this would be the expected behaviour:
Your slicers return different things: The first returns a range of periods and the secod one just one period. So you should expect different results.
I would recommend to start to create a mockup file that you can share here. This might help you in debugging the business logic you've used and narrow down the current culprits in your model.
