cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mira_abel
Helper I
Helper I

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
mira_abel
Helper I
Helper I

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
mira_abel
Helper I
Helper I

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

v-janeyg-msft
Community Support
Community Support

Hi, @mira_abel 

 

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors