Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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