DAX to return Value with the LATEST forecast ID

[Anonymous]

I am trying to derive the DAX = "Total Latest Forecast" BUT i cannot get the total to sum to 1003 + 3002 +2004 =6009 ....  Instead I get 2004 . Can you help ?

[Anonymous]

excellent.. thank you.. i see by summaring, you are returing only forecast relevant to the max date.. then sum that expression.. 

[Mohammad_Refaei]

Exactly, simple and straightforward.

[Mohammad_Refaei]

You're alomst there, just need to adjust your filter a little.

Total Latest Forecast = 
CALCULATE (
    SUM ( [Total Forecast] ),
    FILTER ( ALLEXCEPT ( Fact_Forecast, Fact_Forecast[client] ),
    [fcst cutoffdate] = MAX ( [fcst cutoffdate] )
    )
)

You may check this sample file

[Anonymous]

i notice your total = 2004 instead of 6009.... is there a way where it sums up to 6009

[Mohammad_Refaei · Accepted solution]

Well, there is another solution in this case:

Total Latest Forecast =
SUMX (
    SUMMARIZE (
        Fact_Forecast,
        Fact_Forecast[client],
        "Date", MAX ( Fact_Forecast[fcst cutoffdate] ),
        "Total Forecast", MAX ( Fact_Forecast[Total Forecast] )
    ),
    [Total Forecast]
)

You may redownload the sample file if you want, it is updated.