topic Rolling sum of sales for three years in DAX Commands and Tips

Rolling sum of sales for three years

Anonymous — Tue, 31 Jan 2023 20:00:24 GMT

Hi,

I would like to calculate a rolling sum of sales for three years, the current and two previuos ones. An example is below:

FreemanZ — Wed, 01 Feb 2023 02:22:09 GMT

hi Anonymous

try like:

NewCalculatedColumn =

VAR _year = [Year]

RETURN

SUMX(

FILTER(

TableName,

TableName[Year] IN {_year, _year-1, _year-2}

TableName[Sales]

)

FreemanZ — Wed, 01 Feb 2023 02:23:34 GMT

hi Anonymous

or you can plot a table visual with the Year column and a measure like:

Measure =

VAR _year = MAX(TableName[Year])

RETURN

SUMX(

FILTER(

ALL(TableName),

TableName[Year] IN {_year, _year-1, _year-2}

TableName[Sales]

)

Anonymous — Wed, 01 Feb 2023 14:25:54 GMT

This solution works well for me, thank you!

Anonymous — Thu, 02 Feb 2023 16:12:11 GMT

I am just trying to finilise my query. How would the measure look like if one more colomn, i.e. 'colour', had to be introduced?