cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User
Super User

@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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors