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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Annual Average Headcount consisting of two measures - Actual + Forecast months

Dear DAX community,

Will you please help me with the DAX calculation?

I have a DAX measure for average monthly headcount for:
 - actual months, based on certain selection, e.g. ledger K, Account Code, Journal Type, etc
 - forecast months, based on differnt selection criteria, e.g. ledger F, Account Code, Journal Type, Budget Marker, etc

I cannot figure out how to set up DAX so that it calculates average annual headcount based on the two sets of numbers:

mira_abel_0-1624984010609.png

At the moment I created a very long DAX code, partly because I would like it to be very dynamic, i.e. a user can select a forecast version (Journal Use on screen). For the same reason of dynamic selection I cannot make it a calculate column.... I think...
However, the average (total line) for the year is still a sum of the two measures. It should be annual average 602.39 rather than a sum shown now as 1199.24


Any suggestions??

Measure test 3 =
VAR MonthInUse = SELECTEDVALUE(Fct_JrnType[Month])
VAR DateInUse = DATE([_CurrentYear], MonthInUse,1)
VAR FctDateMarker = DATE([_CurrentYear], MonthInUse+1,1)
VAR FCT_YTG = CALCULATE(
[Forecast MonthAvg Headcount (K, TranAmt) Cal'd rel],
FILTER(
VALUES(_db_sun[AccPeriod]
),
_db_sun[AccPeriod] > DateInUse
),
FILTER(
VALUES(_db_sun[Transaction Date]
),
_db_sun[Transaction Date]=FctDateMarker
)
)
VAR PERIOD_ACT = CALCULATE(DISTINCTCOUNT('Calendar'[MonthNumber]),
FILTER( 'Calendar',
'Calendar'[AccPer_#date] <= DateInUse))

VAR PERIOD_FCT = CALCULATE(DISTINCTCOUNT('Calendar'[MonthNumber]),
FILTER( 'Calendar',
'Calendar'[AccPer_#date] > DateInUse))
 
VAR ACT_YTD = CALCULATE(
[Actual MonthAvg Headcount (K, TranAmt) Cal'd rel],
FILTER(
VALUES(_db_sun[AccPeriod]),
_db_sun[AccPeriod]<= DateInUse
)
)

VAR ACT_YTD_ANNUAL = CALCULATE(
[Actual StatHeadcount (K, TranAmt) Cal'd rel],
FILTER(
VALUES(_db_sun[AccPeriod]),
_db_sun[AccPeriod]<= DateInUse
)
)

VAR FCT_YTG_ANNUAL = CALCULATE(
[Forecast StatHeadcount (F, TranAmt) Cal'd rel],
FILTER(
VALUES(_db_sun[AccPeriod]
),
_db_sun[AccPeriod] > DateInUse
),
FILTER(
VALUES(_db_sun[Transaction Date]
),
_db_sun[Transaction Date]=FctDateMarker
)
)
VAR AVERAGE_CALC = IF ( HASONEFILTER('Calendar'[AccPer_#date]), ACT_YTD +FCT_YTG, ACT_YTD_ANNUAL/ PERIOD_ACT+ FCT_YTG_ANNUAL/ PERIOD_FCT)

RETURN AVERAGE_CALC


Many thanks,

Mira
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hello

I would like to let you know - just in case you will come with the same issue/task - the only way I could get the calculation in DAX for AVERAGEX for a mix of actual and forecast data was through Power Query. I marked rows for each forecast version, ie. 2+10 would have all actual from Ledger A for period 1 and 2 and 10 months from Ledger F for period 3 to 12, etc. Via list functions. Then from the row context it was easy to calculate AVERAGE of mixed data.

Kind regards
Mira

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

Hello

I would like to let you know - just in case you will come with the same issue/task - the only way I could get the calculation in DAX for AVERAGEX for a mix of actual and forecast data was through Power Query. I marked rows for each forecast version, ie. 2+10 would have all actual from Ledger A for period 1 and 2 and 10 months from Ledger F for period 3 to 12, etc. Via list functions. Then from the row context it was easy to calculate AVERAGE of mixed data.

Kind regards
Mira

v-janeyg-msft
Community Support
Community Support

Hi, @Anonymous 

 

Can you share some sample data and your calculation logic and your desired result? Your description is not specific, it is difficult for us to imagine and modify.

Reference the steps:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

 

Best Regards

Janey Guo

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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