Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Average Headcount Measure for Fiscal Year

Hi, 

 

I'm getting a totally different figure below for my Average Headcount for Fiscal Year 23, can someone please check my measure below and correct where I've gone wrong. My page has a Fiscal Year filter and the FY Start Month is April in my dates calendar.

 

Any help with this is much appreciated!

 

Avg. Headcount FY = 
VAR v_fydates = DATESINPERIOD(DimDates[Date], MAX(DimDates[Date]),12,MONTH)
RETURN
AVERAGEX(v_fydates, [Headcount])

 

 

 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    The below measure works and brings my average headcount to 10766 which matches the Excel calculation in my screen shot.

    Avg. Headcount FY = AVERAGEX(VALUES(DimDates[Month & Year]),[Headcount])

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    The below measure works and brings my average headcount to 10766 which matches the Excel calculation in my screen shot.

    Avg. Headcount FY = AVERAGEX(VALUES(DimDates[Month & Year]),[Headcount])

     

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Anonymous , Try like

     

    Avg. Headcount FY =
    VAR v_fydates = DATESINPERIOD(DimDates[Date], MAX(DimDates[Date]),12,MONTH)
    RETURN
    calculate(AVERAGEX(Values(DimDates[Month Year]), [Headcount]),v_fydates)

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi amitchandak 

       

      I've appplied the above measure but the figure is comming up to 11112, any other ways to do this?

  • AjithPrasath's avatar
    AjithPrasath
    Icon for Resolver II rankResolver II
    3 years ago

    Anonymous ,

     

    You can use the below code:

     

    Avg. Headcount FY =
VAR v_fydates =
    DATESINPERIOD(
        DimDates[Date],
        MAX(DimDates[Date]),
        12,
        MONTH
    )
RETURN
    AVERAGEX(
        FILTER(DimDates, DimDates[Date] IN v_fydates),
        [Headcount]
    )

     

     

    Best Regards,

    Ajith Prasath

     

    If this post helps, then please consider Accept it as the solution and give kudos to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi AjithPrasath 

       

      Thanks but this is giving me the same figure as Mar-23 11013 in my card visual, is there another way to write this so I can get the accurate figure for the 12 months in a fiscal year example provided in my screenshots? 

       

      Any help with this will be great  for my development.

      • AjithPrasath's avatar
        AjithPrasath
        Icon for Resolver II rankResolver II
        3 years ago

        can you try this

         

        Avg. Headcount FY =
CALCULATE(
    AVERAGE([Headcount]),
    DATESINPERIOD(
        DimDates[Date],
        MAX(DimDates[Date]),
        -12,
        MONTH
    )
)