Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to calculate monthly average

I have a Date column and I need to calculate the average value for each month by repeating that value for every day of that month.

Example,

DateValueAverage
1/1/2021 150100
1/2/2021 100100
1/20/2021 50100
2/5/2021 300200
2/10/2021 100200
General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 

    Add the following column:

     

    Avg = 
var __my = FORMAT(Table[Date],"mmyy") return
CALCULATE(
    AVERAGE(Table[Value]),
    FILTER(
        Table,
        format(Table[Date],"mmyy") = __my)
)

     

1 Reply

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Anonymous 

    Add the following column:

     

    Avg = 
var __my = FORMAT(Table[Date],"mmyy") return
CALCULATE(
    AVERAGE(Table[Value]),
    FILTER(
        Table,
        format(Table[Date],"mmyy") = __my)
)