Average calculation

Question

Hello ,&nbsp;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 whatthatformula would be?

data-estdog · Answer

Avg7Days =Var vDate = DATEVALUE(FORMAT(NOW(), "yyyy-MM-dd"))Var vDateMinus7 = DATEVALUE(FORMAT(NOW() - 7, "yyyy-MM-dd"))var vAvg = CALCULATE(&nbsp; &nbsp; AVERAGE(Sheet2[Value]), &nbsp; &nbsp; Sheet2[Create Date] &gt;= vDateMinus7&nbsp; &nbsp; &amp;&amp; Sheet2[Create Date] &lt;= vDate)Return vAvg&nbsp;If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

vanessafvg · Answer

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

mart1980 · Answer

Thank you for this. would this formula work to calculate the average of another measure ? I have just tried it and its not giving methe option to add that other measure into the formula

vanessafvg · Answer

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

data-estdog · Answer

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)&nbsp;Avg7DaysFromMeasure =Var vDate = DATEVALUE(FORMAT(NOW(), "yyyy-MM-dd"))Var vDateMinus7 = DATEVALUE(FORMAT(NOW() - 7, "yyyy-MM-dd"))var vAvg = CALCULATE(&nbsp; &nbsp; AVERAGEX(&nbsp; &nbsp; &nbsp; &nbsp; SUMMARIZE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED(Sheet2),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "TotalDays", [aaSUmDays] /*My measure*/&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; [TotalDays]&nbsp; &nbsp; ), &nbsp; &nbsp; Sheet2[Create Date] &gt;= vDateMinus7&nbsp; &nbsp; &amp;&amp; Sheet2[Create Date] &lt;= vDate)Return vAvg&nbsp;

Forum Discussion

Average calculation

5 Replies

Related content

DAX Measure for Ranking the valid rows and calculating the average rating

Calculating Weighted Average

Calculating average 12 month average

Average Calculation

Average calculation