3 data-point lookback average

[Anonymous]

Hello, I have a linegraph with data on the y-axis and time on the x-axis. I am trying to take the average of the last three data points of each point of time, and display it on a secondary y-axis. The table should look something like this:

Is there an easy way to replicate this in powerBI? Please note that the first two data points for column C are just the averages of available data for the column.

Thank you for your time!

[Ashish_Mathur]

Hi,

Don't you have a proper date column?

[Anonymous]

Hello,

I have made a date column, but I am not using it. I am currently using a binned column that groups the dates into two week intervals.

[Ashish_Mathur]

Hi,

With a Date column, it will become simple to solve the question.  Share data in a format that can be pasted in an MS Excel file.

[amitchandak]

@Anonymous , you can use window function

Rolling 3 based on Date = CALCULATE([Net], Window(-2,REL,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

Rolling 3Based on Date = CALCULATE([Net], Window(-2,REL,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

https://www.youtube.com/watch?v=GpoITi_tRIw&list=PLPaNVDMhUXGax-j-mEF8ZxL5eV-VcmdvJ&index=5

[Anonymous]

Hello, I've replicated your solution for my purposes and am not quite getting what I'm looking for. I've attached what I'm getting below, could you please take a look and point me in the correct direction?

My date column (2_weeks) is not in my date table, it's in my data table as a bin of my resolved_date, where essentially it is binned for every 14 days. The sum of story points is the sum of differing integer values within the corresponding bin. The rolling capacity measure is my attempted replication of your work. However, it is clearly not the rolling averages of the sum of the story points, which is what I am attempting to do.