Average based on Index column and dates

[Anonymous]

Hi Experts

How would you calculate the average as shown in the image below,

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table and build a connection between the Month column of your data table and the Date column of the Calendar Table.  In the Calendar Table, extract Year, Month name and Month number via calculated column formulas.  Sort the Month name by month number.  Create slicers for Year and Month name and select July 2021.  Write these measures

Total = sum(data[amount])

Average 3 months = averagex(summarize(filter(calendar,datesbetween(calendar[date],edate(min(calendar[date]),-2),max(calendar[date]))),calendar[year],calendar[month name],"ABCD",[Total]),[abcd])

[sevenhills]

I am not sure whether you want to use the date column or index column in the question.

I assume as you want to do using Index column, give a try and see

6MonthAVG =
VAR MaxVal =
    CALCULATE ( MAX ( 'Table1'[IndexCol] ), ALL ( 'Table1' ) )
RETURN
    CALCULATE (
        SUM ( 'Table1'[Amount] ),
        'Table1'[IndexCol] <= MaxVal && 'Table1'[IndexCol] > (MaxVal - 6)
    ) / 6​

3MonthAVG =
VAR MaxVal =
    CALCULATE ( MAX ( 'Table1'[IndexCol] ), ALL ( 'Table1' ) )
RETURN
    CALCULATE (
        SUM ( 'Table1'[Amount] ),
        'Table1'[IndexCol] <= MaxVal && 'Table1'[IndexCol] > (MaxVal - 3)
    ) / 3