Forum Discussion
Rolling 3 Month Average Help QUICK
Hello,
I have this data and a need a DAX formula to show the rolling 3 month average on the graph based on date 2 for the x-axis and Days between on the Y-axis.
For Example in Feb22 the Average days for a rolling 3 months would be 29.71 (Dec, Jan, Feb)
THANK YOU!
| Year | Month | Date 1 | Date 2 | Days between Date 1 and 2 | Project ID |
| 2021 | October | 9/10/2021 0:00 | 10/4/2021 0:00 | 24 | T8 |
| 2021 | October | 9/23/2021 0:00 | 10/12/2021 0:00 | 19 | T2 |
| 2021 | November | 10/25/2021 0:00 | 11/4/2021 0:00 | 10 | E2 |
| 2021 | November | 11/4/2021 0:00 | 11/18/2021 0:00 | 14 | S1 |
| 2021 | November | 10/6/2021 0:00 | 11/19/2021 0:00 | 44 | T1 |
| 2021 | December | 11/17/2021 0:00 | 12/14/2021 0:00 | 27 | B8 |
| 2021 | December | 11/9/2021 0:00 | 12/15/2021 0:00 | 36 | GW |
| 2022 | January | 12/1/2021 0:00 | 1/5/2022 0:00 | 35 | B9 |
| 2022 | January | 11/22/2021 0:00 | 1/21/2022 0:00 | 60 | C3 |
| 2022 | February | 1/17/2022 0:00 | 2/1/2022 0:00 | 15 | C1 |
| 2022 | February | 1/19/2022 0:00 | 2/1/2022 0:00 | 13 | KC |
| 2022 | February | 1/11/2022 0:00 | 2/2/2022 0:00 | 22 | M2 |
Hi jen8080 ,
Here a potential solution:
For this to work, you need a date dimension in your model. Here a blog post on how you can achieve this:
Create a Date Dimension in Power BI in 4 Steps - Step 1: Calendar Columns - RADACAD
After that you can connect the date dimension with the [date 2] column of your table:
Finally, you can create a measure like this:
TomsRolling3Measure = CALCULATE ( AVERAGE (Table11[Days between Date 1 and 2] ), ALL (Table11), DATESINPERIOD ( 'date'[Dates], EOMONTH ( LASTDATE ( Table11[Date 2] ), 0), -3, MONTH ) )Note, the table in my model is called 'table11'.
Let me know if this solves your issue 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
4 Replies
Hi jen8080 ,
Do you have a date dimension in your data model?
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Hi tackytechtom
I just have the raw data which contains the dates of the acutal deliverables (like date 1 and 2). Then that number that shows the days between dates 1 and 2 is the cycle of calendars between them. Hope this helps!
Hi jen8080 ,
Here a potential solution:
For this to work, you need a date dimension in your model. Here a blog post on how you can achieve this:
Create a Date Dimension in Power BI in 4 Steps - Step 1: Calendar Columns - RADACAD
After that you can connect the date dimension with the [date 2] column of your table:
Finally, you can create a measure like this:
TomsRolling3Measure = CALCULATE ( AVERAGE (Table11[Days between Date 1 and 2] ), ALL (Table11), DATESINPERIOD ( 'date'[Dates], EOMONTH ( LASTDATE ( Table11[Date 2] ), 0), -3, MONTH ) )Note, the table in my model is called 'table11'.
Let me know if this solves your issue 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/tackytechtom THANK YOU!!! That worked!!
