Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help with making Headcount measure static

Hi Guys, 

I hope all are doing well, 
I have created a Headcount over time measure that looks like such:

Headcount Over Time =
CALCULATE (
COUNTROWS ( 'Resource Facts' ),
FILTER (
VALUES ( 'Resource Facts'[Resource Start Date] ),
'Resource Facts'[Resource Start Date] <= MAX ( 'Calendar'[Date] )
),
FILTER (
VALUES ( 'Resource Facts'[Resource Confirmed End Date] ),
OR (
'Resource Facts'[Resource Confirmed End Date] >= MIN ( 'Calendar'[Date] ),
ISBLANK ( 'Resource Facts'[Resource Confirmed End Date] )
)
)
)

I also have a measure for Attrition over time:

 

Attrition =
CALCULATE (
[Headcount Over Time],
USERELATIONSHIP('Calendar'[Date], 'Resource Facts'[Resource Confirmed End Date])
)
 
I have inactive relationship set:
'Calendar'[Date] 1* 'Resource Facts'[Resource Start Date]
'Calendar'[Date] 1* 'Resource Facts'[Resource Confirmed End Date]
 
 

I want to create a attrition % measure which would look like such = Total Attrition / Total Headcount at the start of Fiscal Year (September 2021) 

I understand that I need to modify my headcount measure in a way that it always displays how many active people we had in a beginning of our fiscal year which starts on 01/Sep/2021
Not sure how to do so. 
Any help is much appreciated as always. 

BR,
Kris
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , Try like

     

    Headcount Over Time =
    CALCULATE (
    COUNTROWS ( 'Resource Facts' ),
    FILTER (
    VALUES ( 'Resource Facts'[Resource Start Date] ),
    'Resource Facts'[Resource Start Date] <= startofyear( 'Calendar'[Date] ,"8/31")
    ),
    FILTER (
    VALUES ( 'Resource Facts'[Resource Confirmed End Date] ),
    OR (
    'Resource Facts'[Resource Confirmed End Date] >= startofyear( 'Calendar'[Date] ,"8/31"),
    ISBLANK ( 'Resource Facts'[Resource Confirmed End Date] )
    )
    )
    )

     

    or

     

    Headcount Over Time =
    CALCULATE (
    COUNTROWS ( 'Resource Facts' ),
    FILTER (
    VALUES ( 'Resource Facts'[Resource Start Date] ),
    'Resource Facts'[Resource Start Date] <=minx('Calendar', startofyear( 'Calendar'[Date] ,"8/31"))
    ),
    FILTER (
    VALUES ( 'Resource Facts'[Resource Confirmed End Date] ),
    OR (
    'Resource Facts'[Resource Confirmed End Date] >= minx('Calendar', startofyear( 'Calendar'[Date] ,"8/31")),
    ISBLANK ( 'Resource Facts'[Resource Confirmed End Date] )
    )
    )
    )