Forum Discussion
Calculating Cumulative sum for last year
- 3 years ago
Well, you have to shift the dates one year back. So, something like:
CALCULATETABLE( // This will move the entire // selected period one year back. DATEADD( 'Date'[DateTime], -1, YEAR ), VAR MaxDate = MAX( 'Date'[DateTime] ) VAR CurrentSelection = FILTER( ALLSELECTED( 'Date' ), 'Date'[DateTime] <= MaxDate ) RETURN CurrentSelection )Put this as the filter under your CALCULATE instead of your FILTER. This will be the running total for the same months but 1 year back. If you want to get the sum for all the selected months, then change your FILTER to be:
CALCULATETABLE( // This will move the entire // selected period one year back. DATEADD( 'Date'[DateTime], -1, YEAR ), ALLSELECTED( 'Date' ) )
Well, you have to shift the dates one year back. So, something like:
CALCULATETABLE(
// This will move the entire
// selected period one year back.
DATEADD(
'Date'[DateTime],
-1,
YEAR
),
VAR MaxDate =
MAX( 'Date'[DateTime] )
VAR CurrentSelection =
FILTER(
ALLSELECTED( 'Date' ),
'Date'[DateTime] <= MaxDate
)
RETURN
CurrentSelection
)
Put this as the filter under your CALCULATE instead of your FILTER. This will be the running total for the same months but 1 year back. If you want to get the sum for all the selected months, then change your FILTER to be:
CALCULATETABLE(
// This will move the entire
// selected period one year back.
DATEADD(
'Date'[DateTime],
-1,
YEAR
),
ALLSELECTED( 'Date' )
)
can you please explain it as i am unable to understand and i have asked the same question. will really be helpful if you could.
I have asked the same question in my this question