Adding a column filter to this date filter

[Anonymous]

I want to calculate the average for the last 3 years by value for each month. Average of 2022, 2021, 2020, one value for January, Feb, March, etc. Where could I put a filter on a column here? Like ID = "200060"

This is what I have so far: 

36Month = CALCULATE (

SUM(DATA_DAILY_MONTHLY[VALUE]),

DATESINPERIOD ( DATA_DAILY_MONTHLY[Year-Mon], MAX ( DATA_DAILY_MONTHLY[Year-Mon]), -3, YEAR ))

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try adding the filter() function in the formula.

36Month =
CALCULATE (
SUM ( DATA_DAILY_MONTHLY[VALUE] ),
FILTER ( DATA_DAILY_MONTHLY, DATA_DAILY_MONTHLY[ID] = "200060" ),
DATESINPERIOD (
DATA_DAILY_MONTHLY[Year-Mon],
MAX ( DATA_DAILY_MONTHLY[Year-Mon] ),
-3,
YEAR
)
)

Best Regards,

Jay

[amitchandak]

@Anonymous , Have a column month year and try like

Last3year1 = CALCULATE(AverageX(Values(DATA_DAILY_MONTHLY[Month Year]), DATA_DAILY_MONTHLY[VALUE])
,DATESINPERIOD(DATA_DAILY_MONTHLY[DT],LASTDATE(DATA_DAILY_MONTHLY[DT]),-36,MONTH))