Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Average based on Index column and dates

Hi Experts

 

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

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

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

2 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

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

    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