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:
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,
