Extrapolate Data from Last Week Sales

[Anonymous]

Hi there, 

I want a column or measure that takes the average of the last 7 days and stores it in the future dates (Extrapolating).

When data is entered, the forecast value is replaced and the new recorded data is used as part of the calculation (Rolling Filter).

Shown below is some example data and how I want it to look:

Date	Sales
4/28/2018	10	ignored
4/29/2018	5	ignored
4/30/2018	6	part of forecast calculation
5/1/2018	4	part of forecast calculation
5/2/2018	6	part of forecast calculation
5/3/2018	3	part of forecast calculation
5/4/2018	4	part of forecast calculation
5/5/2018	6	part of forecast calculation
5/6/2018	9	part of forecast calculation
5/7/2018	avg	forecasted
5/8/2018	avg	forecasted
5/9/2018	avg	forecasted
5/10/2018	avg	forecasted
5/11/2018	avg	forecasted
5/12/2018	avg	forecasted
5/13/2018	avg	forecasted
5/14/2018	avg	forecasted
infinity	avg	forecasted

[v-yuta-msft]

Hi coreyweiss,

You requirement is to achieve an average value in last 7 days in every row, right? You can create a measure using DAX like below:

Avg = 
VAR DateStart = MAX(Table1[Date]) - 7
VAR DateEnd = MAX(Table1[Date]) - 1
RETURN
CALCULATE(AVERAGE(Table1[Sales]), Table1[Date] >= DateStart && Table1[Date] <= DateEnd)

 

You can refer to PBIX file here: https://www.dropbox.com/s/uibfaml6qe26zum/Extrapolate%20Data%20from%20Last%20Week%20Sales.pbix?dl=0.

Best Regards,

Jimmy Tao

[Anonymous]

Hi Jimmy,

Your average value in the last 7 days works perfectly, Thank you.

However, is it possible to have the historical data in the same measure? I would like to achieve a result like this: 

The data would be similar to what I initially posted in my question. Thanks!

[v-yuta-msft]

Hi coreyweiss,

You want to truncate the date column by previous date and future date and then connect previous date with measure, right.  I'm afraid your requirement can't be achieved because measure will filter values which meets the condition. As a workaround, you can create a line chart and drag column [Sales] and measure [avg] to Values field like below and then you can compare the difference more clearly.

 

Best Regards,

Jimmy Tao