Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Extrapolate Data from Last Week Sales

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 ne...
Need Help
v-yuta-msft's avatar
v-yuta-msft
Community Support
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      v-yuta-msft
      Community Support
      8 years ago

      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