Rolling total last 12 months grand total

[Anonymous]

Hello All,

Need some help please. I have the below sample data with 5 columns - Year, Month, Department, Employee, Leavers. I need to calculate the % turnover (Leavers/Employees) rolling previous 12 month from the month selected in a slicer. So, if May 2019 is chosen then I need to get the % turnover from June 2018 all the way to May 2019 as a total. The problem is that employee cannot summed up by month because the data is a monthly snapshot.

On the report I need to have Year, Month and Department as slicers.

This is what I have come up with so far but the grand total isn't correct if this measure is used in a table

Rolling 12 month turnover =
VAR _StartDate =
    DATE ( YEAR ( MAX ( 'dDate'[Date] ) ) - 1, MONTH ( MAX ( 'dDate'[Date] ) ), DAY ( MAX ( 'dDate'[Date] ) ) )
VAR _EndDate =
    MAX ( 'dDate'[Date] )
VAR DateFilter =
    FILTER ( ALL ( dDate ), dDate[Date] >= _StartDate && dDate[Date] <= _EndDate )
RETURN
    CALCULATE (
        DIVIDE (
            SUM ( 'fact'[Leavers] ),
            AVERAGEX (
                'fact',
                SUM ( 'Employee] )
            )
        ),
        DateFilter
    )

 This is what I'm getting. the grand total should be 11.16% but it is .93%. 

[Anonymous]

Anyone who can shaed some light on this please? @Zubair_Muhammad @Greg_Deckler @parry2k 

[Toerstad · Accepted solution]

Hi again.

I think you may be overthinking this problem.

Try creating a measure for the Turnover by utilizing the SUMMARIZE function, and then create a rolling 12 months measure based on this Turnover-measure.

Create a Turnover-measure like this:

Turnover = SUMX(
	SUMMARIZE('fact', //Summarizes the fact table
		dDate[year-month], //Grouping on the [year-month] column
		"Turnover", //Naming and creating the turnover-measure caluclation for each row (group)
		DIVIDE(
			SUM('fact'[Leavers]),
				SUM('fact'[Employee])
			)
		),
	[Turnover]) //Sums up the values given the evaluation context.

This would result in a measure evaluating to the correct Turnover for each year-month, and the correct total turnover for the Grand Total row.

Now you could use time intelligence functions like TOTALYTD, TOTALQTD, to craete new measures based on your turnover-measure. And you could create a measure to calculate the Rolling12M Turnover like this:

Rolling12M Turnover = CALCULATE (
    [Turnover],
    DATESBETWEEN (
        dDate[Date],
        NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(dDate[Date]))),
        LASTDATE(dDate[Date])
    )
)   

When you create DAX measures it is often usefull to break down your process in several smaller steps, and then build more on top of them.

[Anonymous]

This solution works for me, thank you very much for your help. I was indeed overthinking it!

[Toerstad]

Hi.

You can solve this by utilizing the function HASONEFILTER. This can be used to change the formula used for a measure given the evaluation context.

Put the name of your yaer-month column in the HASONEFILTER function.

Rolling 12 month turnover =
IF(HASONEFILTER('dDate'[year-month]), // <-- put your year-month column here. 

// if HASONEFILTER is true it will use the following 1 month window
VAR _StartDate =
    DATE ( YEAR ( MAX ( 'dDate'[Date] ) ) - 1, MONTH ( MAX ( 'dDate'[Date] ) ), DAY ( MAX ( 'dDate'[Date] ) ) )
VAR _EndDate =
    MAX ( 'dDate'[Date] )
VAR DateFilter =
    FILTER ( ALL ( dDate ), dDate[Date] >= _StartDate && dDate[Date] <= _EndDate )
RETURN
    CALCULATE (
        DIVIDE (
            SUM ( 'fact'[Leavers] ),
            AVERAGEX (
                'fact',
                SUM ( 'Employee] )
            )
        ),
        DateFilter
    ), 

// if HASONEFILTER is false (as it woult be for the Total) it will use the following 12 month window
VAR _StartDate =
    DATE ( YEAR ( MAX ( 'dDate'[Date] ) ) - 12, MONTH ( MAX ( 'dDate'[Date] ) ), DAY ( MAX ( 'dDate'[Date] ) ) )
VAR _EndDate =
    MAX ( 'dDate'[Date] )
VAR DateFilter =
    FILTER ( ALL ( dDate ), dDate[Date] >= _StartDate && dDate[Date] <= _EndDate )
RETURN
    CALCULATE (
        DIVIDE (
            SUM ( 'fact'[Leavers] ),
            AVERAGEX (
                'fact',
                SUM ( 'Employee] )
            )
        ),
        DateFilter
    )
)

You can probably find a more efficient way of wrinting the measure utilizing the HASONEFILTER function yourself. You can find the reference for the function here: https://docs.microsoft.com/en-us/dax/hasonefilter-function-dax 

[Anonymous]

Hi Toerstad,

Thank you but this does not seem to work as expected. 

Sample data 

2019	3	A	37
2019	3	B	187
2019	3	C	39	1
2019	3	D	66	1
2019	3	E	10
2019	3	F	10
2019	3	G	5
2019	3	H	10
2019	3	I	37
2019	3	J	9
2019	3	K	42
2019	3	L	4
2019	3	M	14
2019	3	N	4	1
2019	3	AN	3
2019	3	O	13
2019	3	P	8
2019	3	Q	9	2
2019	3	R	1
2019	3	S	10
2019	3	T	7
2019	3	U	9
2019	3	V	17
2019	3	W	48
2019	3	Y	2
2019	3	Z	19
2019	3	AA	21
2019	3	AB	40
2019	3	AC	9
2019	3	AO	2
2019	3	AD	29
2019	3	AE	13
2019	3	AF	31
2019	3	AG	1
2019	3	AH	7
2019	3	AI	6
2019	3	AJ	50
2019	3	AK	32
2019	3	AL	6
2019	3	AM	3		870	5	0.57%
2019	4	A	37
2019	4	B	188	2
2019	4	C	38
2019	4	D	66
2019	4	E	9
2019	4	F	10
2019	4	G	5
2019	4	H	10
2019	4	I	37
2019	4	J	9
2019	4	K	42
2019	4	L	5
2019	4	M	14
2019	4	N	4
2019	4	AN	2
2019	4	O	12	1
2019	4	P	8	1
2019	4	Q	7	1
2019	4	R	1
2019	4	S	10
2019	4	T	7
2019	4	U	9
2019	4	V	17
2019	4	W	48	1
2019	4	Y	3
2019	4	Z	18
2019	4	AA	21
2019	4	AB	39
2019	4	AC	9
2019	4	AO	2
2019	4	AD	29	2
2019	4	AE	13
2019	4	AF	31	1
2019	4	AG	1
2019	4	AH	7
2019	4	AI	6
2019	4	AJ	50
2019	4	AK	32
2019	4	AL	6
2019	4	AM	3		865	9	1.04%
2019	5	A	37
2019	5	B	193	2
2019	5	C	38	2
2019	5	D	66
2019	5	E	9
2019	5	F	10
2019	5	G	5
2019	5	H	10
2019	5	I	37
2019	5	J	9
2019	5	K	41
2019	5	L	4
2019	5	M	14
2019	5	N	4
2019	5	AN	2
2019	5	O	10
2019	5	P	7
2019	5	Q	6
2019	5	R	1
2019	5	S	9
2019	5	T	7
2019	5	U	9
2019	5	V	17	1
2019	5	W	47	2
2019	5	Y	3
2019	5	Z	18	1
2019	5	AA	22
2019	5	AB	39
2019	5	AC	9
2019	5	AO	2
2019	5	AD	27
2019	5	AE	11	1
2019	5	AF	30	1
2019	5	AG	1
2019	5	AH	7
2019	5	AI	6
2019	5	AJ	50	1
2019	5	AK	33
2019	5	AL	5
2019	5	AM	3		858	11	1.28%

[Toerstad]

Hi.

Since you have a snapshot table you could rewrite the entire last part, where HASONEFILTER = False.
A calculation dividing the sum of all the "leavers" for the last 12 months and utilizing the LASTNONBLANK function to get the values for current employees should work. 

See documentation on the LASTNONBLANK function here: https://docs.microsoft.com/en-us/dax/lastnonblank-function-dax

And common patterns and scenarios for semi-additive measures here: https://www.sqlbi.com/articles/semi-additive-measures-in-dax-for-power-pivot/