calculate average for past 7 days

[kfschaefer]

Is there a function to calculate the (current date-1)-7 to display the average over the past 7 days not including current date?

If 4/21/2016 then 4/20/2016-7 days for the average of the Revenue for those dates.

day        Revenue

1             25000

2             50000

3             23000

4            14000

5            14000

6            18000

7             5000

so the average = 21285.71

thanks,

K

[v-caliao-msft]

Hi Kfschaefer,

I have tested it on my local environment, we can use the create a measure to achieve this requirement.
Sample data  table name:MovingAverage

create 3 measures.
SumAmount = sum(MovingAverage[Amount])
7 Dyas Moving Sum = CALCULATE([SumAmount],DATESINPERIOD(MovingAverage[Date],LASTDATE(MovingAverage[Date]),-7,DAY))
7 Days Moving Agerage = [7 Dyas Moving Sum]/CALCULATE(DISTINCTCOUNT(MovingAverage[Date]),DATESINPERIOD(MovingAverage[Date],LASTDATE(MovingAverage[Date]),-7,DAY))

Regards,

[Anonymous]

Hi,

I have the data recorded exactly same for 2years i/e., starting 2019 till date(moving data), I have performed the 7-day moving average as mentioned by you, but when I try to plot(line plot) the same with having month as the axis and year as legend , it shows me the 7-day moving average recorded on the last date each month, any particular reasons for this ?(if that's wrong, what else it should be displayed when we plot over a month)

Thanks

[Sean]

@kfschaefer You have to FIRST SORT your data by DATE in Excel and then create your AVERAGE formulas

[Sean]

@kfschaefer

7 Day Avg = DIVIDE(CALCULATE(SUM(Table1[Revenue]), DATESINPERIOD(Table1[Date], LASTDATE(Table1[Date])-1, -7, DAY) ), 7, 0)

NOTE: You may or may not need the -1 after last date! Depends on your setup!

[kfschaefer]

Thanks for the input, I was wondering would this be the same as the Average function in Excel?  Your suggestion does not return the same results of the Average function that I am already using in an Excel workbook.

[Sean]

I'm not exactly sure what you mean - it returns exactly what you said you want to return...

EDIT: You probably don't want a 7 day average.

You probably want an average of the number of days you have in your table no matter what it is?

If you have 2 - average 2, 3 average 3, etc...

[kfschaefer]

I need to clarify I am looking for a running 7 day average.  Note the changes in the range.  so Please verify that your calculation will accommplish this task.

thanks,

Karen

[Sean]

@kfschaefer so let me get this straight

On April 2nd - you want to calculate the 7 Day Average for (Apr 1 to Apr 😎 - go back 1 day and 5 forward

and then repeat this pattern forward - will you always have the next 5 day's Revenue?

Moving average is Trailing LAST 7 days

[kfschaefer]

Not sure we are on the same page.  I want to have a running average of revenue not includeing the current date.  My data is always update the previous night..

when I used your calculation it does not match the numbers I get when I create the average in a excel sheet.

thanks fo your asssistance.

[Sean · Accepted solution]

@kfschaefer Just a final friendly warning.

The way you have it setup in Excel AVERAGE is not giving you divided by 7 in ALL cells!

Create a separate column and see for yourself => check manually => add the 7 Revenues and divide by 7 (write /7)

do this for all your dates => let me know if you get the same Averages as AVERAGE( ) is giving you?

[kfschaefer]

I did as you suggested and in Excel they match.  These are my actual numbers:Note I do not want to go forward 7 days I want 7 days back from the previous day.  since the data is updated the previous night.

DATE	RevenueTotal	GrossProfit	Average	Divby7
4/9/2016	171803.2	44577.782	$  258,056.14	$  258,056.14
4/8/2016	259121.6	80276.0568	$  261,603.36	$  261,603.36
4/7/2016	294520.9	89633.0404	$  245,195.94	$  245,195.94
4/6/2016	279327.1	88114.9839	$  244,123.73	$  244,123.73
4/5/2016	276287.1	91224.3605	$  244,865.79	$  244,865.79
4/4/2016	306763.8	103206.383	$  221,237.12	$  221,237.12
4/3/2016	150015.3	51042.4229	$  222,401.30	$  222,401.30
4/21/2016	240357	131852.7619	$  221,278.85	$  221,278.85
4/20/2016	283952.2	149949.3079	$  218,733.51	$  218,733.51
4/2/2016	179669.1	60052.2179	$  234,398.90	$  234,398.90
4/19/2016	271821.7	111286.0846	$  238,031.15	$  238,031.15
4/18/2016	281481.6	109654.9519	$  243,253.90	$  243,253.90
4/17/2016	141363.1	48207.9115	$  245,061.23	$  245,061.23
4/16/2016	158164.6	57136.4325	$  260,689.56	$  260,689.56
4/15/2016	232499.8	88224.8922	$  265,226.47	$  265,226.47
4/14/2016	266134.8	86360.8837	$  263,450.56	$  263,450.56
4/13/2016	289326.8	85511.0529	$  263,655.34	$  263,655.34
4/12/2016	297247.4	83415.0322	$  242,205.10	$  242,205.10
4/11/2016	318040.8	97461.8463	$  222,608.51	$  222,608.51
4/10/2016	154014.4	50339.5701	$  233,424.77	$  233,424.77
4/1/2016	267562.9	83532.1773	$  233,876.51	$  233,876.51
3/9/2016	264258.2	98625.921	$  237,646.68	$  237,646.68

[kfschaefer]

thanks for all your work, I ended up modifying the SQL and not in BI.

[ImkeF]

One could have solved this is the query editor using M with an additional column:

= Table.AddColumn(Source, "Custom", (FilterTable) => List.Sum(
                                Table.SelectRows(
                                    Table.Buffer(Source),
                                    (ValuesTable)=> ValuesTable[date]>Date.AddDays(FilterTable[date],-8)
                                    and ValuesTable[date]<FilterTable[date] )[Revenue]
                                ))

see: https://social.technet.microsoft.com/Forums/en-US/1275f33f-71df-41ee-914f-c482d2f0678e/sumifs-in-pow...

[Sean]

@ImkeF Just for the record so does my formula! Try it out!

7 Day Avg = DIVIDE(CALCULATE(SUM(Table1[Revenue]), DATESINPERIOD(Table1[Date], LASTDATE(Table1[Date])-1, -7, DAY) ), 7, 0)

[ImkeF]

Yes, this should deliver the same result.

However, there seems to be an issue with keeping the sort order when loading to the datamodel. So you should buffer the step where you apply the order. Actually: You should apply the order at the last step of your query and buffer that last step in order to make sure it sticks. Maybe that's the reason why it gave problems.

[Sean]

@kfschaefer The picture shows how I calculate the 7 Day Average in Excel and PBI.

I've highlighted the 7 Day Average for 4/10/16 and how it's calculated - have no idea how you come with your numbers?