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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors