Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ariver
Frequent Visitor

Add Results of a different Measure for last 12 months

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 NameTerminationsEmployeesMeasure for TurnoverLast 12 monthsLast 12 months for month before
2/1/2020214864.32%  124.32%
3/1/2020315036.16%126.16%116.16%
4/1/2020395287.39%117.39%107.39%
5/1/2020395427.20%107.20%97.20%
6/1/2020365536.51%96.51%86.51%
7/1/2020485508.73%88.73%78.73%
8/1/2020445398.16%78.16%68.16%
9/1/2020215393.90%63.90%53.90%
10/1/2020405497.29%57.29%47.29%
11/1/2020195423.51%43.51%33.51%
12/1/2020265404.81%34.81%24.81%
1/1/2021205383.72%23.72%13.72%
2/1/2021195563.42%13.42%  
    SUM70.80%SUM71.70%
    TTM0.05900TTM0.0598

 

Any suggestions?

1 ACCEPTED SOLUTION
v-angzheng-msft
Community Support
Community Support

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:

vangzhengmsft_0-1647235712576.png

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.

View solution in original post

5 REPLIES 5
ariver
Frequent Visitor

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

Hi,

Share the raw data Tables and show the expected result. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Community Support
Community Support

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:

vangzhengmsft_0-1647235712576.png

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.

These are the results I am getting now:

ariver_0-1649083369027.png

 

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

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.