Home Forums Power BI calculating the last 12 months

This topic contains 1 reply, has 2 voices, and was last updated by  mgrisham 7 months, 3 weeks ago.

Viewing 2 posts - 1 through 2 (of 2 total)
• Author
Posts
• #9487

Rich_Coutinho
Participant
• Started: 10
• Replies: 16
• Total: 26

I have a excel file with Payroll number, Hiring date, Leaving date and Etc that i updated monthly… I have created in Power BI a calendar and some measures to calculate the headcount, the leavers, new joiners…

Now I need to calculate the attraction Turnover with this formula:

`the leavers in the last 12 months / ((headcount of the month in previous year + headcount of month in current year)/2)`

Putting simply:

1. for each month i need to have the last 12 months leavers :  MAR 2018 should be (MAR 2017 until FEB 2018), FEB 2018 should be ( FEB 2017 until JAN 2018), JAN 2018 (JAN 2017 until DEC 2017), DEC 2017 (DEC 2016 until NOV 2017) and so on …
2. For each month should be calculated the sum of headcount for the last and current year:  MAR 2018 and MAR 2017, FEB 2018 and FEB 2017, JAN 2018 and JAN 2017, DEC 2017 and DEC 2016 and so on…..

example 1 calculation for MAR 2018 :

leavers in the last 12 months (MAR 2017 until FEB 2018) = 15

Headcount MAR 2017 =  474,

Headcount for March 2018 =  506

the calculation should be : 15/((474 + 506)/2) = 3.06 %

Example 2 calculation for DEC 2017:

Leavers in the last 12 months from (DEC 2016 until NOV 2017)= 10

headcount DEC 2016 =479

headcount for  2017 = 508

so the calculation should be  10/((479+508)/2) =  2.02%

Does somebody have any idea how can i calculate it in powerBI?

###### Attachments:
You must be logged in to view attached files.
#10118

mgrisham
Participant
• Started: 1
• Replies: 6
• Total: 7

this post has always helped me when working with date functions, should be simple enough to combine with your date table:

https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.