cancel
Showing results 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.

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!

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

1 ACCEPTED SOLUTION
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])``

6 REPLIES 6
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])``

Resolver II

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

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.

Resolver III

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.

Resolver II

can you try this

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

@GJ217 , Try like

VAR v_fydates = DATESINPERIOD(DimDates[Date], MAX(DimDates[Date]),12,MONTH)
RETURN

Resolver III

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

Announcements

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

Power BI Monthly Update - April 2024

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

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors