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 = "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'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

2 Replies

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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