Forum Discussion
How to Calculate Rolling Average?
I have a dataset with a date column, column called "column" (every value is 1 for simplicity), and a measure for rolling average.
Rolling average =
CALCULATE(
SUM(
vw_BI_Yield_Trend[Column]),
DATESINPERIOD(vw_BI_Yield_Trend[MFG_SYN_OUT_DATE], MAX(vw_BI_Yield_Trend[MFG_SYN_OUT_DATE]), -3, DAY))
The formula seems to work initially but as i scroll from 2006 to 2023 data, I notice the summation does not tabulate right.
10 Replies
- Ashish_Mathur
Super User
Hi,
Try this approach:
- Create a Calendar Table
- Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table
- To your visual, drag the Date column from the Calendar Table
- Write these measures
Total = SUM(vw_BI_Yield_Trend[Column])
Measure = calculate([Total],datesbetween(calendar[date],min(calendar[date])-2,min(calendar[date])))
Hope this helps.
Hi Ashish Mathur, I've tried the above mentioned, the outcome still looks pretty much unchanged unfortunately
- Ashish_Mathur
Share the download link of the PBI file and shwo the expected result.
