I'm trying to create a power bi report that monitors the organisations employee turnover rate. The Turnover calculation I'm using is below.
No of Leavers Divided by
(Headcount Start of Fiscal Period / Headcount End of Fiscal Period) / 2
I've enclosed a spreadsheet with 2 tabs that I hope will explain what I am trying to achieve.
Tab 1 - The sample of Data
Tab 2 - The desired output.
I hope someone can help
Thanks in advance.
Solved! Go to Solution.
I'm not so clear for your table structure to coding formula, can you please share some sample data? (your link seem not works)
Apoligies forgot to set the data sample to share, hopefully the below link will work;
Please try to use following dax formulas to achieve your requirement:
Calculate table formula: Output Summary = SUMMARIZE ( Data, Data[FiscalMonthFull], Data[YearMonth], "StartOfMonth", SUM ( Data[HeadcountStartofMonth] ), "EndofMonth", SUM ( Data[HeadCountEndofMonth] ), "Leavers", SUM ( Data[LeaverinMonth] ) ) Calculate column formula: Monthly Turnover = DIVIDE([Leavers],([StartOfMonth]+[EndofMonth])/2,-1) YTD Turnover = CALCULATE ( SUM ( 'Output Summary'[Monthly Turnover] ), FILTER ( ALL ( 'Output Summary' ), YEAR ( [YearMonth] ) = YEAR ( EARLIER ( [YearMonth] ) ) && [YearMonth] <= EARLIER ( [YearMonth] ) ) )
Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.
Join our monthly meetings and learning sessions.
We are excited to announce the Power BI Super Users!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.