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
Need Help
  • 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] )
    )
    )
    )

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Usually in this measure we usually use both min and that means the start of the period. so if visual has FY, It will start of FY

     

    otherwise for MIN ( 'Calendar'[Date] ) or Max ( 'Calendar'[Date] ) use 


    startofyear( 'Calendar'[Date] ,"8/31")

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi amitchandak 
      let me elaborate a bit more.  I will include some screenshots below. 
      So I have a visual that is showing me monthly rolling headcounts that is configured to only display numbers for FY22. This visual is using the [Headcount Over Time] measure described in my previous comment. 

      What I want here is for the visual to display a September number of 127 in ALL of the months. 

      So it should ignore the filter context and return only return the September number (127) in all of the months. 

      BR,

      Kris

       

      • amitchandak's avatar
        amitchandak
        Super User
        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] )
        )
        )
        )