This topic contains 1 reply, has 2 voices, and was last updated by mgrisham 7 months, 3 weeks ago.
-
AuthorPosts
-
March 23, 2018 at 3:45 pm #9487
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:
- 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 …
- 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.June 29, 2018 at 8:37 pm #10118this 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/
-
AuthorPosts
You must be logged in to reply to this topic.