Forum Discussion

Mart1980's avatar
Mart1980
Helper I
2 years ago

Average calculation

Hello ,   I am trying to create a measure that will basically calculate the average of a value for the last 7 days, but this has to be a calculation that every day it calculates the average of 7 da...
Need Help
Show and Tell
Tips and Tricks
Data-estDog's avatar
Data-estDog
Resolver II
2 years ago
Avg7Days =
Var vDate = DATEVALUE(FORMAT(NOW(), "yyyy-MM-dd"))
Var vDateMinus7 = DATEVALUE(FORMAT(NOW() - 7, "yyyy-MM-dd"))
var vAvg = CALCULATE(
    AVERAGE(Sheet2[Value]),
    Sheet2[Create Date] >= vDateMinus7
    && Sheet2[Create Date] <= vDate
)
Return vAvg
 
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
  • Mart1980's avatar
    Mart1980
    Helper I
    2 years ago

    Thank you for this. would this formula work to calculate the average of another measure ? I have just tried it and its not giving me

    the option to add that other measure into the formula

    • vanessafvg's avatar
      vanessafvg
      Community Champion
      2 years ago

      that is why its best to share your data, so that the correct solution can be given.

    • Data-estDog's avatar
      Data-estDog
      Resolver II
      2 years ago

      Sure, but not directly. You need to apply the measure to every row of the table (sheet2 in my case) using averageX (a row level calc before aggregating)

       

      Avg7DaysFromMeasure =
      Var vDate = DATEVALUE(FORMAT(NOW(), "yyyy-MM-dd"))
      Var vDateMinus7 = DATEVALUE(FORMAT(NOW() - 7, "yyyy-MM-dd"))
      var vAvg =
      CALCULATE(
          AVERAGEX(
              SUMMARIZE(
                  ALLSELECTED(Sheet2),
                  "TotalDays", [aaSUmDays] /*My measure*/
              ),
              [TotalDays]
          ),
          Sheet2[Create Date] >= vDateMinus7
          && Sheet2[Create Date] <= vDate
      )

      Return vAvg