Add Results of a different Measure for last 12 months

[ariver]

Hello,

I am hoping someone can help me find a solution for this. I need to calculate TTM per the following guideline:

TTM = Sum of Turnover for the last 12 months / 12

Turnover = Terminated Employees/ Total Active Employees

The Measure I am using for the Turnover calculation is:

Turnover = IF('TimeCards'[Employees]=0,"N/A",Dates[Termination Count]/'TimeCards'[Employees])

I am not able to find information on how to add the results for the last 12 months of the measure. Example:

Month Name	Terminations	Employees	Measure for Turnover	Last 12 months	Last 12 months for month before
2/1/2020	21	486	4.32%			12	4.32%
3/1/2020	31	503	6.16%	12	6.16%	11	6.16%
4/1/2020	39	528	7.39%	11	7.39%	10	7.39%
5/1/2020	39	542	7.20%	10	7.20%	9	7.20%
6/1/2020	36	553	6.51%	9	6.51%	8	6.51%
7/1/2020	48	550	8.73%	8	8.73%	7	8.73%
8/1/2020	44	539	8.16%	7	8.16%	6	8.16%
9/1/2020	21	539	3.90%	6	3.90%	5	3.90%
10/1/2020	40	549	7.29%	5	7.29%	4	7.29%
11/1/2020	19	542	3.51%	4	3.51%	3	3.51%
12/1/2020	26	540	4.81%	3	4.81%	2	4.81%
1/1/2021	20	538	3.72%	2	3.72%	1	3.72%
2/1/2021	19	556	3.42%	1	3.42%
				SUM	70.80%	SUM	71.70%
				TTM	0.05900	TTM	0.0598

Any suggestions?

[ariver]

Hello, I wasn't able to add my turnover measure into the syntax above. Any suggestions?

[Ashish_Mathur]

Hi,

Share the raw data Tables and show the expected result. 

[v-angzheng-msft]

Hi, @ariver 
I have merged the turnover measure into the TTM measure, and you can replace it with your measure at the DIVIDE function if you wish.

_TTM2 =
CALCULATE (
    // DIVIDE(
    //     Calculate(SUM('Table'[Terminations])),
    //     Calculate(SUM('Table'[Employees]))
    //     ),
    [yourTrunOver_measure],
    FILTER (
        'Table',
        'Table'[Month Name] <= EOMONTH ( MAXX ( ALL ( 'Table' ), [Month Name] ), -1 )
            && 'Table'[Month Name] > EOMONTH ( MAXX ( ALL ( 'Table' ), [Month Name] ), -13 )
    )
)

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-angzheng-msft · Accepted solution]

Hi, @ariver 

Try to create measures as following:

_SUM2 = 
SUMX(
    FIlter(
        ADDCOLUMNS('Table',"_turnover",[_TTM2]),
        'Table'[Month Name]<=EOMONTH(MAXX(ALL('Table'),[Month Name]),-1)&&'Table'[Month Name]>EOMONTH(MAXX(ALL('Table'),[Month Name]),-13)),
    [_turnover]
    )

_TTM2 = 
Calculate(DIVIDE(Calculate(SUM('Table'[Terminations])),Calculate(SUM('Table'[Employees]))),
FIlter('Table','Table'[Month Name]<=EOMONTH(MAXX(ALL('Table'),[Month Name]),-1)&&'Table'[Month Name]>EOMONTH(MAXX(ALL('Table'),[Month Name]),-13)))

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ariver]

These are the results I am getting now:

I am going to prepare the data and share I am working with, I am not sure what I am doing wrong.