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
mbl
Frequent Visitor

I would like to calculate headcount month over month based on hiredate.

Input Raw Data  
Hire DateNameLocation
10/29/2019AAAA
10/30/2019BBBB
3/5/2012CCCC
5/10/2018EAAA
6/7/2018FBBB
8/25/2017GCCC
9/13/2016HAAA
1/19/2020IBBB
2/21/2020ICCC
3/19/2020IAAA
3/9/2020IBBB

 

 

 

Desired Output1  
Number of Headcount per Month as on 2020Headcount by month 
Jan'208 
Feb'209 
Mar'2011 
3 REPLIES 3
Thejeswar
Resident Rockstar
Resident Rockstar

@mbl ,

You can create a measure with the below DAX and use for this purpose

 

running headcount = CALCULATE(COUNT('Table (2)'[Name]), FILTER(ALL('Table (2)'), 'Table (2)'[Hire Date] <= MAX('Table (2)'[Hire Date])))

 

Thejeswar_0-1594036879662.png

 

 

Regards,

 

AntrikshSharma
Community Champion
Community Champion

Something like this? You can use date table to slice the data.

mBI.PNG

MBI 2.PNG

HeadCount = COUNTROWS( HireData )
Running Headcount =
VAR Result =
    CALCULATE (
        [HeadCount],
        FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
    )
RETURN
    IF ( [HeadCount] > 0, Result, BLANK () )

 

harshnathani
Community Champion
Community Champion

HI @mbl ,

 

See if this helps.

 

Watch this video from 5:38 onwards.

 

https://www.youtube.com/watch?v=rsx43g7TBBs

 


Regards,

Harsh Nathani


Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

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.

Top Solution Authors