Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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. Th...
Need Help
Tutorial Requests
amitchandak's avatar
amitchandak
Super User
3 years ago

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's avatar
    Anonymous
    Not applicable
    3 years ago

    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.