topic Re: Cumulative Measure in Line Graph works, but not with date hierarchy in DAX Commands and Tips

Cumulative Measure in Line Graph works, but not with date hierarchy

Brannon — Thu, 02 Feb 2023 16:01:37 GMT

I have a cumulative measure, and when I have the chart broken down to weeks the measure works as expected, but when rolling up to months my cumulative line continues to the end of the date field. Everything is calculating correctly, I just am not getting the actual cumulative line to stop at the data date.

By week:

By Month:

Here is the dax I am using:

Actual Cumulative =

IF(selectedvalue(Dates[Merged])<=(LASTDATE('Curr Data'[Data Date])-6),

CALCULATE (

SUMX ( filter('Curr Data','Curr Data'[Spreadsheet Field] = "Actual Units" || 'Curr Data'[Spreadsheet Field] = "Period Actual Units"),'Curr Data'[Value] ),

FILTER (

ALL(Dates[Merged]),

(Dates[Merged]<= max(Dates[Merged]))

))

)

Thanks for any help you folks may have.

Brannon

Re: Cumulative Measure in Line Graph works, but not with date hierarchy

johnt75 — Thu, 02 Feb 2023 17:08:01 GMT

Try changing SELECTEDVALUE to MAX. SELECTEDVALUE will return blank if multiple dates are visible in the filter context, and blank evaluates as less than any date.

Re: Cumulative Measure in Line Graph works, but not with date hierarchy

Brannon — Thu, 02 Feb 2023 17:23:52 GMT

THANK YOU! I've been searching all over for a solution for that, MAX worked.