Forum Discussion
Adding a column filter to this date filter
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...
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
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
)
)
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