Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
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
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!
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
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |