cancel
Showing results for
Did you mean:

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

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 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?

1 ACCEPTED SOLUTION
Community Support

Hi, @ariver

Try to create measures as following:

``````_SUM2 =
SUMX(
FIlter(
'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.

5 REPLIES 5
Frequent Visitor

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

Super User

Hi,

Share the raw data Tables and show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

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.

Community Support

Hi, @ariver

Try to create measures as following:

``````_SUM2 =
SUMX(
FIlter(
'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.

Frequent Visitor

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.

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors