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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sraizada
Frequent Visitor

Calculating Monthly Turnover from YTD total

Hi

 

I have a dashboard that calculates YTD turnover. I now want to enhance it to track monthly turnover to be able to show trends comparing Dec 2019 to Dec 2018 for example.

However, my turnover calculation for avg. headcount has a beginning year headcount and since I don't want to add monthly tables since its a desktop model, I have the year-end headcount as the ending headcount.

To calculate the turnover in any month in the middle of the year, I have the hire date. I also have a terminations table, that gives me the terminations every month based on termination date.

My question is, can I calculate monthly headcount and then turnover based on these two (Hire and termination dates?) even when my dataset is for the entire year. 

For instance, I was thinking to calculate monthly headcount, I add the hires in the intervening period to the beginning headcount and take out the terminations in the intervening period?

For the termination table, I can add the terminations in the intervening period and take out future dated terminations. Would this work? 

What would be the easiest way to do this? Do i need to create a date table? I already have a Key Link table in the data model for slicers by year, business unit and location, should I add month to that or create a new date table?

Sorry about the lenghty post but any help would be appreciated.

 

thanks!

1 ACCEPTED SOLUTION

Hi @sraizada ,

First, I created a calendar table:

 

Table = 
CALENDAR(
    DATE(2019,1,1),
    DATE(2019,12,31)
)
// the column will be used in the slicer
YYYY-MM = FORMAT( 'Table'[Date], "YYYY-MM")

 

aa10.PNG

 

Second, I created a measure:

 

headcount = 
CALCULATE(
    COUNT(Sheet6[Employee ID]),
    FILTER(
        Sheet6,
        Sheet6[Hire Date] <= FIRSTNONBLANK('Table'[Date], 1) 
        && 
        Sheet6[Termination Date] >= FIRSTNONBLANK('Table'[Date],1)
    )
)

 

aa11.PNG

 

Best regards,
Lionel Chen

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

4 REPLIES 4
v-lionel-msft
Community Support
Community Support

Hi @sraizada ,

Did you have two tables like this?

oo1.PNGoo2.PNG

What's the relationship between the two tables?

As you see, I can't made the data model. can you give me a sample data model.

 

Best Regards,
Lionel Chen

Hi Lionel,

 

Thanks for your revert. I only have one table with the hire date and term date. I have the same table snapshot for two dates, one from beginning of the year (Jan) and one from year end (Dec). The two snapshots are appended to each other. 

I want to calculate the May headcount for instance based on this data, how do I do that by comparing hire and termination dates?

-Shweta

Employee IDHire DateTermination Date
99763/3/20035/22/2015
997111/2/20156/22/2016
98743/2/2018 

Hi @sraizada ,

First, I created a calendar table:

 

Table = 
CALENDAR(
    DATE(2019,1,1),
    DATE(2019,12,31)
)
// the column will be used in the slicer
YYYY-MM = FORMAT( 'Table'[Date], "YYYY-MM")

 

aa10.PNG

 

Second, I created a measure:

 

headcount = 
CALCULATE(
    COUNT(Sheet6[Employee ID]),
    FILTER(
        Sheet6,
        Sheet6[Hire Date] <= FIRSTNONBLANK('Table'[Date], 1) 
        && 
        Sheet6[Termination Date] >= FIRSTNONBLANK('Table'[Date],1)
    )
)

 

aa11.PNG

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lionel,

 

Thanks for the solution,this works well. Will this also work for multiple years? Say, 2018, 2019, 2020. I can change the calendar table to include those years.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.