Incorrect Grand Total

[Andrea_Jess]

Hi all, 

I have this DAX formula to basically calculate sum of amount for months after selected date. 

Show Future Forecast =

var a = SUM(Data[Amount])

var b = MAX(Data[Month for Finacial Date])

var c = MAX(Data[Year for Finacial Date])

RETURN

if (b <= SELECTEDVALUE(DimDate[MonthNum]) && c = SELECTEDVALUE(DimDate[Year]) , BLANK(), a)

This is what it returns. The Totals for Amount and the Totals for Show Future Forecast are the same but it shouldn't be since Show Future Forecast is missing two months. Is anyone able to assist and advise why?

[TomMartens]

Hey @Andrea_Jess ,

Forecast and Amount are the same in the Total Line, as there is no active filter for year month in the total.

You can solve this using the table iterator function SUMX SUMX – DAX Guide.

Your measure then will be similar to this:

Show Future Forecast = 
SUMX(
    SUMMARIZE(
        '<yourcalendartable>'
        , '<yourcalendartable>'[year]
        , '<yourcalendartable>'[month]
    )
    , ... // the numeric expression
)

The numeric expression might look like this:

var a = CALCULATE( SUM(Data[Amount]) )
var b = CALCULATE( MAX(Data[Month for Finacial Date]) )
var c = CALCULATE( MAX(Data[Year for Finacial Date]) )
RETURN
if (b <= SELECTEDVALUE(DimDate[MonthNum]) && c = SELECTEDVALUE(DimDate[Year]) , BLANK(), a)

The CALCULATE is necessary to transform the existing ROW context that has been created by the table iterator function SUMX into a filter context.

Hopefully, this provides what you are looking for to tackle this challenge.

Regards,

Tom

[Andrea_Jess]

Hi Tom,

Thanks for that. That makes sense. Unfortunately, i'm still getting the same totals even with the addition of the SUMX function. This is what i'm using now. Is there something incorrect i'm doing here?

SUMX(

SUMMARIZE(

'DimDate'

, 'DimDate'[Year]

, 'DimDate'[Month]),

var a = CALCULATE( SUM(Data[Amount]) )

var b = CALCULATE( MAX(Data[Month for Finacial Date]) )

var c = CALCULATE( MAX(Data[Year for Finacial Date]) )

RETURN

if (b <= SELECTEDVALUE(DimDate[MonthNum]) && c = SELECTEDVALUE(DimDate[Year]) , BLANK(), a))

[TomMartens]

Hey @Andrea_Jess ,

please create a pbix file that contains sample data but still reflects your data model (tables, relationships between tables, calculated columns, and measures). Upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method share the xlsx as well.
Please explain the expected result based on the sample data you provided.

Regards,

Tom