Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |