CODEWITHSUNDEEP
aggregationdax
Sauron
Sauron

Reputation: 6647

DAX TOTALQTD does not show GrandTotal properly

Using the DAX function to build out the Quarter to Date calculations:

Measure 1 QTD:=TOTALQTD([Measure 1],DATESYTD(DimDate[Date],"09-31"),ALL(DimDate))

it functions properly aside from the Grand Total:

enter image description here

What I need is for the GrandTotal not to be the last quarter to date sum, but the total year end. In this case it would be ~$915,000.

How can DAX to configured to permit this?

Upvotes: 1

Views: 354

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

You are asking it to perform a different computation for the Grand Total. To do this you need to have some sort of condition to let it know when you want to compute the Grand Total and when you want to compute everything else.

One possibility is to use the HASONEVALUE function like this:

IF(HASONEVALUE(DimDate[Date],
    TOTALQTD([Measure 1], DATESYTD(DimDate[Date], "09-30"), ALL(DimDate)),
    TOTALYTD([Measure 1], DimDate[Date], ALL(DimDate), "09-30"))

This should give you QTD when you have a single date filter context and YTD for the Grand Total since it has multiple dates values in the filter context.

Upvotes: 1

Related Questions