Rolling values per category

l6903b5 — Thu, 23 Feb 2023 12:43:58 GMT

I need help with DAX.

I have this table:

business unit	year_month	category	amount	amount all categories	12 month rolling sum	12 month rolling sum all categories	12 month mov avg %
PS	202206	A	4	10	4	10	40%
PS	202207	A	6	9	10	19	53%
PS	202208	A	6	10	16	29	55%
PS	202209	A	3	4	19	33	58%
PS	202210	A	9	18	28	51	55%
PS	202211	A	3	7	31	58	53%
PS	202212	A	0	0	31	58	53%
PS	202301	A	5	12	36	70	51%
PS	202302	A	11	21	47	91	52%

I have filtered Year-month >= 202206 because column category is blank before this date. I have filtered on 1 business unit and 1 category, but there are more in de dataset. Amount all category = amount of all categories in business / year-month.

I have a date table. I need the Dax formula for the 12 month rolling sum. This is wat I have thus far:

12 month rolling sum =

var amount = COUNT('Table'[category])

Return CALCULATE (amount, DATESINPERIOD (Calendar[DATE],LASTDATE ( Calendar[DATE] ),-365,DAY))

But it gives my the same as column amount.

topic Rolling values per category in DAX Commands and Tips

Rolling values per category