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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors