How to Calculate Rolling Average?

[bryanang]

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.

[bryanang]

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.

[bryanang]

https://app.powerbi.com/links/m3emMryV2_?ctid=b67d722d-aa8a-4777-a169-ebeb7a6a3b67&pbi_source=linkSh...

Does this work for you?

[Ashish_Mathur]

That takes me to a sign-in page.

[bryanang]

Seems like I cannot share with you because you're outside of the organisation, im sorry. But it seems like the issue could be because I have duplicate rows in my date. (Have filtered it down in the data view tab but didn't know that the filter gets stripped off in the report view)

[bryanang]

Yeap its uploaded to the power bi service, would need you to log in with an account.

[Ashish_Mathur]

I obviously cannot log in there.  Share the download link of the PBI file.

[bryanang]

https://thermofisher.sharepoint.com/:u:/s/GulCircleTier3Meeting/EYp80ym1B4ZNqbcp4LqdcqYB9iig2KCw4WoB...

What about this?

[Ashish_Mathur]

I get a sign-in page.

[Ashish_Mathur]

Hi,

Try this approach:

1. Create a Calendar Table
2. Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table
3. To your visual, drag the Date column from the Calendar Table
4. 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.