Summary Table - Headcount on Last Day and New Starters across Month

[bigrods]

Hi,

I wondered if anyone could help; I would like to summarize a table of employee headcount and new starters. The table should display the following 3 columns:

Month Name	HeadCount	New Starters

However, the Head count should be taken as at the figure on the final day of each month, and the New Starters should be a sum of all the new starters across each month.

I have a 'Calendar' date table, this also has 2 calculated columns in:

Timeframe - to identify if the date is in the last 12 months

LastDay - to identify if the date is the last date in the month

Calendar Table

I also have a HeadCount Dataset table; this lists:

Employee Number

Latest Start Date

Asg Effective End Date

I have also created 2 measures, I wondered if it was possible to fit these in to the table?

HeadCount Measure:

New Starters Measure:

I am struggling with how to get a mix of - last day of the month and a sum across the month - if anyone could please help that would be much appreciated!

Many thanks

[ryan_mayu]

pls provide some sample data of headcount table and the expected output

[bigrods · Accepted solution]

Thanks - I have managed to solve myself, I had a Date table that listed each day "01-Apr", "02-Apr", "03-Apr" etc.

This was causing me the headache but I solved it by adding a new calculated column of FORMAT('calendar'[Date],"mmm-yy").

This then let me create the Summarized table that calculates the Headcount, New Starters and Leavers for each month!

Headcount Summary = SUMMARIZE('calendar','calendar'[DateMonth],"HeadCount",CALCULATE(DISTINCTCOUNT('Headcount Dataset'[Employee Number]),

    FILTER('Headcount Dataset','Headcount Dataset'[Latest Start Date] <= MAX('calendar'[Date]) && ('Headcount Dataset'[Asg Effective End Date] >= min('calendar'[Date]) || ISBLANK('Headcount Dataset'[Asg Effective End Date])) && ('Headcount Dataset'[termination] >=MIN('calendar'[Date]) || ISBLANK('Headcount Dataset'[termination])))),"New Starters",CALCULATE(DISTINCTCOUNT('Headcount Dataset'[Employee Number]),

    FILTER(VALUES('Headcount Dataset'[Latest Start Date]),'Headcount Dataset'[Latest Start Date] <= MAX('calendar'[Date]) && 'Headcount Dataset'[Latest Start Date] >= MIN('calendar'[Date]))),"Leavers",CALCULATE(DISTINCTCOUNT('Headcount Dataset'[Employee Number]),

    FILTER(VALUES('Headcount Dataset'[Termination]),'Headcount Dataset'[termination] <= MAX('calendar'[Date]) && 'Headcount Dataset'[termination] >= MIN('calendar'[Date]))))

[Anonymous]

Hi @bigrods ,

Glad to hear you've found a solution! If you're sure the issue has been resolved, could you mark this post as resolved? That way, others with similar issues can more easily find a solution and the community can see that the issue has been resolved.
Thanks, and feel free to reach out if you need further help!