Running Total Measure Works By Day but Not by Week

[Anonymous]

Hello,

I am having a problem with displaying the running total of a column in a Line and Stacked Column Chart visual.

This visual is to show forecasted and actual shipping numbers.

The X axis has the option of Fiscal Quarter, Fiscal Month, Fiscal Week, and Calendar Date.

When drilling down by Calendar Date, the running total works as expected. Green is forecasted shipping, and blue is actual. Red is the difference

But it only shows the sum for the given time period when drilling down any other way. (In other words, it's not cumulative.)

Here is the measure for the running total (Green Line):

CALCULATE(

    [Shipment Forecast],

    FILTER(

        ALLSELECTED('Fiscal Calendar'[Calendar Date]),

        ISONORAFTER('Fiscal Calendar'[Calendar Date], MAX('Fiscal Calendar'[Calendar Date]), DESC)

    )

)

Thanks in advance!

[Anonymous · Accepted solution]

Edited:

[The Right Measure] =
var MaxFiscalDate = MAX( 'Fiscal Calendar'[Calendar Date] )
var Result =
	CALCULATE(
	    [Shipment Forecast],
	    KEEPFILTERS( 'Fiscal Calendar'[Calendar Date] <= MaxFiscalDate ),
	    ALLSELECTED( 'Fiscal Calendar' )
	)
return
	Result

There would be too much to explain about how ALLSELECTED works...

[Anonymous]

@Anonymous 
This does work as a running total, but I don't want it to count anything before the selected period. So in filters from my example, nothing before Fiscal Q2 of 2021.

[Anonymous]

I've edited my measure... which now works as expected.

[Anonymous]

This works for the green line on my visual!
Unfortunatley both the green line and the blue columns (Total Shipped) were having the same issue, and following the same logic with Total Shipped didn't work. In fact, it made it not cumulative when drilling down on both Calendar Date and Fiscal Week.

Here is the original measure:

Total Shipped running total in Fiscal Week =

CALCULATE(

    [Total Shipped],

    FILTER(

        ALLSELECTED('Fiscal Calendar'[Calendar Date]),

        ISONORAFTER('Fiscal Calendar'[Calendar Date], MAX('Fiscal Calendar'[Calendar Date]), DESC)

    )

)

And here is the one I tried to model off of your solution:

Total Shipped running total in Fiscal Week =

var MaxFiscalDate = MAX( 'Fiscal Calendar'[Calendar Date] )

var Result =

    CALCULATE(

     [Total Shipped],

     KEEPFILTERS( 'Fiscal Calendar'[Calendar Date] <= MaxFiscalDate ),

     ALLSELECTED( 'Fiscal Calendar' )

    )

return

    Result

[Anonymous]

@Anonymous 

I created a simple model to test it and it works the way it should. If it does not work in your case, then it means... there's something amiss about your model.

[Anonymous]

I think you must be right. Thank you for the help!

[Anonymous]

@Anonymous After fixing my model, the issue was resolved. However I noticed that that when I select multiple options in any slicer, the running total resets after that time interval. With one selected or none in each slicer, there are no issues. In the example below, I have weeks 1-5 selected.

The Total Shipped (Blue) and Shipment Forecast (Green) follow the format of your measure above. Any idea on why this is happening?

[Anonymous]

Instead of going back and forth aimlessly and fruitlessly... please place a link to the file in here. I'll then resolve all the issues you have. Promise.

[Greg_Deckler]

@Anonymous What happens if you base your running total on Fiscal Week instead of the Date?

[Anonymous]

@Greg_Deckler 
I tried changing the running total measure to 

OPs Ship Fcst running total in Fiscal Week =

CALCULATE(

    [Shipment Forecast],

    FILTER(

        ALLSELECTED('Fiscal Calendar'[Fiscal Week]),

        ISONORAFTER('Fiscal Calendar'[Fiscal Week], MAX('Fiscal Calendar'[Fiscal Week]), DESC)

    )

)

But the visual looks the same. In addition when drilling down by calendar date, it no longer shows the running total.

[Greg_Deckler]

@Anonymous Not entirely certain but perhaps you need ALL not ALLSELECTED. Very hard to decipher what may be going on without something to play with.

[Anonymous]

@Greg_Deckler This works as a running total, but I need this visual to not count anything before the selected period. So in filters from my example, nothing before Fiscal Q2 of 2021.