Forum Discussion
Running Total Measure Works By Day but Not by Week
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...
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
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.I've edited my measure... which now works as expected.
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' ))returnResult