Reply
avatar user
Anonymous
Not applicable
Partially syndicated - Outbound

3 data-point lookback average

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:
Lookback_Internals.PNG

 

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!

5 REPLIES 5
Ashish_Mathur
Super User
Super User

Syndicated - Outbound

Hi,

Don't you have a proper date column?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
avatar user
Anonymous
Not applicable

Syndicated - Outbound

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.

Syndicated - Outbound

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

Syndicated - Outbound

@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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
avatar user
Anonymous
Not applicable

Syndicated - Outbound

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?

PowerBI_Lookback_Issue.PNG

 

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.

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)