Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
GJ217
Resolver III
Resolver III

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!

 

Average Headcount FY 2.jpgAverage Headcount FY.jpg

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

 

 

 

1 ACCEPTED SOLUTION
GJ217
Resolver III
Resolver III

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

 

View solution in original post

6 REPLIES 6
GJ217
Resolver III
Resolver III

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
Resolver II
Resolver II

@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.

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.

can you try this

 

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

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

Hi @amitchandak 

 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.