Forum Discussion
Running Total Measure Works By Day but Not by Week
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):
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 ResultThere would be too much to explain about how ALLSELECTED works...
12 Replies
- Greg_Deckler
Community Champion
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.
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.
- Greg_Deckler
Anonymous What happens if you base your running total on Fiscal Week instead of the Date?
Greg_Deckler
I tried changing the running total measure toOPs 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.
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 ResultThere would be too much to explain about how ALLSELECTED works...
