Average Headcount Measure for Fiscal Year

[GJ217]

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

[GJ217 · Accepted solution]

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]

@GJ217 ,

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.

[GJ217]

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]

can you try this

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

[amitchandak]

@GJ217 , 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)

[GJ217]

Hi @amitchandak 

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