Column Subtotal Issue

[plerner16]

I have a measure that pulls Actuals data from Jan-Current Month and Forecasted data for the rest of the year with a formula: 

CALCULATE(IF(

    Max(Master_Date[Daily]) <= Today(),

    Sheet1[Actuals],

    Sheet1[FCST]))

The issue is that when I filter on the full year, it only shows Forecast totals, but if I filter down to August (The last month of Actuals) then it'll show me the correct Subtotal for Jan-Aug. I need to figure out how to make it show me Jan-Dec subtotals where it sums Jan-Aug Actuals and then Sep-Dec Actuals.

[Greg_Deckler]

@plerner16 I agree with @AmiraBedh, adding some content here that may help you understand what is going on. First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[AmiraBedh · Accepted solution]

The problem here is that when you filter to the full year, the context of your measure doesn't know how to differentiate between months that should use `Actuals` and those that should use `Forecasted` data.

You need to iterate through each day in your current context and determine whether to take `Actuals` or `Forecasted` data for that day.

One approach is to use the `SUMX` function.

Total Actuals and Forecast =
SUMX (
VALUES ( Master_Date[Daily] ),
IF (
Master_Date[Daily] <= TODAY(),
CALCULATE ( SUM ( Sheet1[Actuals] ) ),
CALCULATE ( SUM ( Sheet1[FCST] ) )
)
)