Stop cumulative total from resetting in matrix from date slicer?

[jd8766]

I have the below measure that gives me the running total of contracts per month (in a matrix visual), however when I change the slicer selection for end date I don't want the cumaltive number to recalculate, I want it to always show the cumilating total from the first end date and the last end date...regardless of whats selected in slicer. The slicer should just be used to see what the running total was at any specific time selected. Is there something obvious I am missing in my measure to achieve this?

Running Total = CALCULATE(

sum([count]),

FILTER(

ALL(Query3[End Date].[Date]),

ISONORAFTER(Query3[End Date].[Date], MAX(Query3[End Date].[Date]), DESC)

)

)

[AlexisOlson]

I'd suggest making the cumulative total a calculated column since you don't want it to depend on the date slicer.

[jd8766]

Sorry, I am confused how that would work? I still want to be able to see the below view (end date at the top, sold date on the left) with my running totals.. I just don't want it to recalculate when I change the end date in the slicer

[AlexisOlson]

Instead of a measure, you define the running total as a calculated column.

The wrinkle here is that a running total is not an additive measure, so to use it in your visualization, you'll need to use MAX over that calculated column instead of the default SUM (assuming your cumulative total is increasing).

[jd8766]

Thanks, I added a calculated column in SQL (before it gets loaded in PBI)
count(*) OVER( ORDER BY enddate)

However using the value of this column in Power BI isn't giving me the expected results.. 

[AlexisOlson]

Something is definitely fishy here but I can't tell quite what without seeing what the data is shaped like and what dimensions you're using for rows and columns.

[jd8766]

It's quite a small simple table I am using, like the below..

My measure to count contracts is just a countrows measure. I can get my cumulative total working absolutely fine.. using this measure...

Running Total = CALCULATE(

countrows(query3),

FILTER(

ALL(Query3[End Date].[Date]),

ISONORAFTER(Query3[End Date].[Date], MAX(Query3[End Date].[Date]), DESC)

)

)

it's just getting theend date filter to not reset the cumulativetotal i can't understand...

[AlexisOlson]

Yes, but you aren't (directly) using any of these columns for the rows and columns of your matrix visual. It's auto-creating hidden date dimension tables so that time intelligence on months and years is possible. Without the auto date/time, you can write a running total calculated column as:

Running Total =
VAR _CurrDate = Query3[End Date]
RETURN
    CALCULATE ( COUNTROWS ( query3 ), Query3[End Date] <= _CurrDate )

You have better control if you turn off the automatic time intelligence and work with a proper date dimension table or two. You may want multiple date tables since you have a start date and an end date.

[PaulDBrown]

Turn off the Interaction between the date slicer and the running total visual (under format -> Edit interactions)

[jd8766]

yeah thats the tricky part.. we want it to interact with the visual because the customer still wants to select an end date range to view... they just dont want the running total to be reset when they select a date range.

[PaulDBrown]

You can use a copy of the date table for the dates in the cumulative visual. The model is like this:
The measure:

Cal 2 Cumulative =
CALCULATE (
    [Sum of Sales],
    REMOVEFILTERS ( 'Calendar Table'[Date] ),
    FILTER (
        ALL ( Cal2 ),
        Cal2[Date] <= MAX ( Cal2[Date] )
            && Cal2[Year] = MAX ( Cal2[Year] )
    )
)

Create a measure to filter the visual based on the dates selected from the calendar slicer. Add the measure to the filter pane for the visual and set the value to greater or equal to 1

Filter Cumulative =
IF (
    MAX ( Cal2[Date] ) >= MIN ( 'Calendar Table'[Date] )
        && MAX ( Cal2[Date] ) <= MAX ( 'Calendar Table'[Date] ),
    1
)

The visual has the fields from the cal2 table; the slicer is from the regular Calendar Table. 

[jd8766]

Thanks for this... but I still can't get it to work for some reason? I have attached an example pbix file... the bottom table works perfect. The issue is i only want the columns to change when the slider is selected (to show the dates seected within the slider)

I don't want the cumulative total resetting..

Can you see in the attached why I am having this issue?
DEMO.pbix 

[PaulDBrown]

Is this what you are after? 

[jd8766]

Hmm I can't seem to get the figures I want from this... and it should also be in the same format as the attached.

Are you able to replicate the figures below? If so then I must be doing something wrong..

The end date it across the top, sold date is on the left.. and these are the figures I get with an end date of 2020 which is correct, but I can't seem to get them using your measures?

[PaulDBrown]

What's  the measure in the visual?

[jd8766]

the measure that gives me the correct output in the demo I attached is the [perf running total] measure.

I want the figures this gives me... I just dont want the cumulative total to reset when the slider changes