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 Ye...
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])

     

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
    )
)