Swapping Previous month's forecasts with that same month's actuals but YTD is wrong

[PowerIBM]

I've got two attributes, i.e., forecasts and actual sales. When a month passes, my dax replaces forecast values with actual sales values. If we talk about MTD of forecast, it's working fine, but when it comes to YTD, it reflects the total of forecast by considering its own original values and doesn't account for the replaced values for the total.

My DAXs are as follows:

OpEx_AOP =

VAR _AOP =

SWITCH([Calendar Order],

0, [AOP Selected Value],

1, CALCULATE([AOP Selected Value], DATESQTD('Date Table'[Date])),

2, CALCULATE([AOP Selected Value], DATESYTD('Date Table'[Date])))

RETURN

_AOP

OpEx_AOP_F =

IF(MAX('Date Table'[date]) < (DATE(YEAR(TODAY()), MONTH(TODAY()), 1) -1),[ACT_Currency],PLAN_OpEx[OpEx_AOP])

What could be the solution for this?

[johnt75]

I think its failing because the max date is being calculated at the total level, and you need to calculate it at the daily level. Try

OpEx_AOP =
VAR _AOP =
    SWITCH (
        [Calendar Order],
        0, [AOP Selected Value],
        1, SUMX ( DATESQTD ( 'Date Table'[Date] ), [AOP Selected Value] ),
        2, SUMX ( DATESYTD ( 'Date Table'[Date] ), [AOP Selected Value] )
    )
RETURN
    _AOP