Average calculation

[Mart1980]

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 days before so I cant have a set start date and end date. Can you please let me know what

that

formula would be?

[vanessafvg]

please provide an sample of what your data looks like, in text format. 

[Data-estDog]

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]

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

[Data-estDog]

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

[vanessafvg]

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