I know this has been somewhat asked but what I'm trying to do has a slightly different presentaion and I'm having a hard time figuring out how to create a rolling average.
Below is a sample of how my data is presented.
DATE THING VALUE
What I'm trying to do is, have another column to the right of VALUE that shows the average VALUE for each thing over the last number of days (15 or 30 for example).
Thanks in advance for the help
Have you looked at my rolling weeks Quick Measure?
No I hadn't seen it. I'm not well versed in DAX and don't know how to extract knowledge from there.
The two key differences that I see comparing it with my data are:
Nevertheless thank you for your response.
Total = SUM(Data[Value])
Average over the past 30 days = CALCULATE([Total],DATESBETWEEN(Calendar[Date],MIN(Calendar[Date])-30,MIN(Calendar[Date])))
Hope this helps.
Hey thanks for you answer, I tried that DAX function and adjusted slightly because it would give me an error
Average over the past 30 days = CALCULATE(Total = SUM(Data[Value]),DATESBETWEEN(Calendar[Date],MIN(Calendar[Date])-30,MIN(Calendar[Date])))
It worked but the numbers that it gave me are much lower than what the averages actually should be.
I will need to see your file to know the error you are committing. Also, please meniton there what the correct answer should be.
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.
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])))