topic Re: Adding a column filter to this date filter in DAX Commands and Tips

Adding a column filter to this date filter

Anonymous — Fri, 24 Jun 2022 13:40:16 GMT

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 ))

Re: Adding a column filter to this date filter

amitchandak — Fri, 24 Jun 2022 05:31:22 GMT

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))

Re: Adding a column filter to this date filter

Anonymous — Wed, 29 Jun 2022 09:37:07 GMT

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