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.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors