Cumulative/Running Total

[jake223]

Hello,

I'm having an issue with creating a Cumulative/Running Total. Currently I have the following:

• StartFTE_TO = sum('92_FOM_FY20'[Current FTE for TO])
• EndFTE_TO = SUM('92_FOM_FY20'[Current FTE for TO]) - sum('95_Terms_FY20'[Current FTE for TO]) + sum('98_Hires_FY20'[Current FTE for TO]) + sum('97_TransfersIn_FY20'[Current FTE for TO]) - sum('97_TransfersOut_FY20'[Current FTE for TO]) + sum('96_PromotionsIn_FY20'[Current FTE for TO]) - sum('96_PromotionsOut_FY20'[Current FTE for TO]) - sum('92_FOM_FY20'[Inactive Seasonal])
• TermsFTE_TO = sum('95_Terms_FY20'[Current FTE for TO])
• Turnover = ([TermsFTE_TO] / (([StartFTE_TO]+[EndFTE_TO]) / 2))
• Cumulative = CALCULATE([Turnover], FILTER(ALLSELECTED('7_Date'), '7_Date'[Date] <= MAX('7_Date'[Date])))

Below I'm using "FiscalMonthLong" as a Filter in my report from a Date Table (Marked as Date Table). The Turnover column is calculating correctly but as you can see my Cumulative column is not adding correctly. Also the "Total" row does not seem to calculate correctly unless I change this to a waterfall chart which displays the correct total. 

[ImkeF]

Hi @jake223 ,

the problem lies in your matrix visual. Once you pull a column that indicates the year into the rows section, the values should turn out fine. As there is probably more than one June in your calendar table, it is not clear which year is meant - and therefore the order cannot be determined.

[jake223]

Hi @ImkeF 

I updated my date table From Start Date: 6/1/2016 To: 6/1/2019 the start of our fiscal year (June in the Image). and it still seems it only calculating the current month. But I do a think it might have to do with using Fiscal year just not sure how/what/why. 

Thanks for all your Help!

[amitchandak]

Seem like it is doing Avg. Can you share the formula.

[jake223]

Hi @amitchandak 

I used the Quick measure to create both fields below are the formulas

• Turnover running total in FiscalMonthLong = CALCULATE([Turnover],FILTER(CALCULATETABLE(SUMMARIZE('7_Date', '7_Date'[FiscalMonthNum], '7_Date'[FiscalMonthLong]),ALLSELECTED('7_Date')),ISONORAFTER('7_Date'[FiscalMonthNum], MAX('7_Date'[FiscalMonthNum]), DESC,'7_Date'[FiscalMonthLong], MAX('7_Date'[FiscalMonthLong]), DESC)))
• Turnover YTD = TOTALYTD([Turnover], '7_Date'[Date])

[amitchandak]

Calculation of turnover

[jake223]

@amitchandak 

Turnover = ([TermsFTE_TO] / (([StartFTE_TO]+[EndFTE_TO]) / 2))

[tex628]

Hello @jake223 ,

Try changing from:

Cumulative = CALCULATE([Turnover], FILTER(ALLSELECTED('7_Date'), '7_Date'[Date] <= MAX('7_Date'[Date])))

to 

Cumulative =
VAR maxDate = MAX('7_Date'[Date])
Return
CALCULATE([Turnover], ALL('7_Date'), '7_Date'[Date] <= maxDate))

Br,
J

[jake223]

@tex628 

I tried using and looks like that same result

Cumulative =

VAR maxDate = MAX('7_Date'[Date])

RETURN

CALCULATE([Turnover], ALL('7_Date'), '7_Date'[Date] <= maxDate)

[tex628]

Just to make sure, you are using a slicer on year here together with the table right? 

What do you get if you just add:

Cumulative =

VAR maxDate = MAX('7_Date'[Date])

RETURN

maxDate

[jake223]

@tex628 

No other filters on the Page or Visual. This is what I get with maxDate

[tex628]

Am I correct in assuming you're looking for the following numbers here? 
0,017
0,036
0,059
0,059

[jake223]

@tex628 

yes that is correct 

[ImkeF]

So you want to add up your averages?

Then you need a SUMX.

Create a virtual table with a year and a month-column, add the [TurnoverMeasure] at the row level and add it up.

Something like:

VAR CurrentMonth = MAX(YourDateTableName[YourMonthColumnName] ),
VAR CurrentYear = MAX(YourDateTableName[YourMonthColumnName] )

VAR IteratorTable = 
ADDCOLUMNS( 

   FILTER(

        CROSSJOIN( VALUES(YourDateTableName[YourYearColumnName]) ,  VALUES(YourDateTableName[YourMonthColumnName]) ),

       YourDateTableName[YourYearColumnName] <= CurrentYear,

       YourDateTableName[YourMonthColumnName] <= CurrentMonth 

    ),
    "Turnover_", [Turnover] )
RETURN
SUMX ( IteratorTable, [Turnover_] )

[jake223]

Hi @ImkeF 

I think i'm a little lost on this one. Do you have anything to reference?

[ImkeF]

Hi @jake223 ,

How about you mocking up some sample data? That would allow me to apply the function to your case.

I don't have samples for my solution at hand and would have to google as well.

[jake223]

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

[tex628]

@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. 

[jake223]

Hi @tex628 

This is what I get

[jake223]

Hello @tex628  @ImkeF 

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.

[ImkeF]

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.