Forum Discussion
13 Week Rolling Average Graph with Daily %
How can I send the file here?
Below is the table and the column on the right shows the values that I'd like to get automatically.
Please note that for this example, the rolling average is calculated based on the last 3 days (not 30), that's why it starts showing on the fourth day. (This is manually calculated)
The DAX code that I've used is this:
AVERAGE_CALCULATED = CALCULATE(SUM(Sheet1[VALUE])=SUM(Sheet1[VALUE]),DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date])-3,MIN('Calendar'[Date])))And it displays only 1's on every row.
Thanks again
| DATE | TYPE | VALUE | AVERAGE |
| 4/1/2018 | A | 7 | |
| 4/1/2018 | B | 6 | |
| 4/1/2018 | C | 3 | |
| 4/2/2018 | A | 8 | |
| 4/2/2018 | B | 7 | |
| 4/2/2018 | C | 9 | |
| 4/3/2018 | A | 6 | |
| 4/3/2018 | B | 5 | |
| 4/3/2018 | C | 5 | |
| 4/4/2018 | A | 6 | 7.0 |
| 4/4/2018 | B | 8 | 6.0 |
| 4/4/2018 | C | 7 | 5.7 |
| 4/5/2018 | A | 5 | 6.7 |
| 4/5/2018 | B | 4 | 6.7 |
| 4/5/2018 | C | 5 | 7.0 |
| 4/6/2018 | A | 9 | 5.7 |
| 4/6/2018 | B | 8 | 5.7 |
| 4/6/2018 | C | 7 | 5.7 |
| 4/7/2018 | A | 6 | 6.7 |
| 4/7/2018 | B | 3 | 6.7 |
| 4/7/2018 | C | 5 | 6.3 |
Hi,
Remove the =SUM(Sheet1[VALUE]) from within the CALCULATE() function.
Does that help?
Not really, it now shows the first three values the same as the first three from the VALUE column (7,6,3) and the rest of the column is empty.
Just to confirm the new formula is:AVERAGE_CALCULATED = CALCULATE(SUM(Sheet1[VALUE]),DATESBETWEEN('Calendar'[Date],MIN('Calendar'[Date])-3,MIN('Calendar'[Date])))
Thanks again
Hi,
Share the link from where i can download your workbook.