Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi
I've trying to calculate the 3,6,9 months rolling average for my data. The challenge I have is that transaction dates are all dated to be 1st of each month. To elaborate more, my data looks like the below. I was wondering how can I calculate the rolling averages in such condition? The sample data in this link
Thanks in advance
Date | Customer | QTY |
1-Jan-21 | A | 200 |
1-Jan-21 | B | 300 |
1-Jan-21 | C | 400 |
1-Jan-21 | D | 500 |
1-Jan-21 | C | 100 |
1-Jan-21 | B | 100 |
1-Jan-21 | A | 200 |
1-Jan-21 | C | 300 |
1-Jan-21 | D | 500 |
1-Jan-21 | D | 100 |
1-Jan-21 | C | 300 |
1-Feb-21 | A | 700 |
1-Feb-21 | B | 600 |
1-Feb-21 | D | 150 |
1-Feb-21 | A | 250 |
Hi @Anonymous ,
We need to remove filters from the context because otherwise the date filters to calculate the rolling x months will not be able to kick in. You could have removed all Filters by using ALL ( ), but then the rolling window would not have been applied properly when doing groupings on customer level. I assumed, that you would have liked to still be able to do such analysis. Hence, I just removed all filters except the one for customers. Another possible way could have been to explicitly remove the filter from dates, like this:
TomsRolling3MonthMeasureWithoutAllExcept = CALCULATE ( AVERAGE (Table[QTY] ), REMOVEFILTERS ( Table[Date] ), DATESINPERIOD ( 'date'[Dates], EOMONTH ( LASTDATE ( Table[Date] ), 0), -3, MONTH ) )
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hi @Anonymous ,
You are right, I did not read your query properly. I'll update the response above. 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Hi @Anonymous ,
How about this:
Here the code for the measures:
TomsRolling3MonthMeasure = CALCULATE ( AVERAGE (Table[QTY] ), ALLEXCEPT ( 'Table', Table[Customer] ), DATESINPERIOD ( 'date'[Dates], EOMONTH ( LASTDATE ( Table[Date] ), 0), -3, MONTH ) )
TomsRolling6MonthMeasure = CALCULATE ( AVERAGE (Table[QTY] ), ALLEXCEPT ( 'Table', Table[Customer] ), DATESINPERIOD ( 'date'[Dates], EOMONTH ( LASTDATE ( Table[Date] ), 0), -6, MONTH ) )
TomsRolling9MonthMeasure = CALCULATE ( AVERAGE (Table[QTY] ), ALLEXCEPT ( 'Table', Table[Customer] ), DATESINPERIOD ( 'date'[Dates], EOMONTH ( LASTDATE ( Table[Date] ), 0), -9, MONTH ) )
Note, it is important to have a gapless date dimension in place.
Let me know if this helps 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
another question
From what I see, I require to create dates table. Which is fine.
May I know why I need to exclude the customer from the context filter?:
ALLEXCEPT ( 'Table', Table[Customer] )
On another note, I don't want the average per customer, but per month. I'll add a filter for customers (Slicer)
thanks @tackytechtom
For average, shouldn't the DAX use Averagex/average instead of Sum?
CALCULATE (
AVERAGE (Table[QTY] ),
ALLEXCEPT ( 'Table', Table[Customer] ),
DATESINPERIOD (
'date'[Dates],
EOMONTH ( LASTDATE ( Table[Date] ), 0),
-9,
MONTH
)
)
Something seems to be not right.
The 3 months and 6 months rolling average is the same.
also, the rolling average doesn't look right. 3 months rolling average on April-2019 should be
I think the fact that all transactions being on 1st of each month is contributing to this and it requires special processing.
The right values for example for 2019
Hi,
You may download my PBI file from here.
Hope this helps.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.