Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I have a table with the list of items and sales by month. How do I calculated monthly rolling average (3 month) by Item? I am trying to create a calcultated column. The example of data is show below
I found a few threads suggesting using AVERAGEX and DATESINPERIOD using calendar table. This does work but doesn't give me data by Part ID.
Part ID | Month | Sales | Moving month average (3M) |
AAA1 | Jan | 110 | |
AAA1 | Feb | 100 | |
AAA1 | Mar | 90 | |
AAA1 | Apr | 77 | |
AAA1 | May | 555 | |
AAA2 | Jan | 200 | |
AAA2 | Feb | 100 | |
AAA2 | Mar | 222 | |
AAA2 | Apr | 350 | |
AAA2 | May | 110 |
Solved! Go to Solution.
Hi @Anonymous
My sample table:
I build a Month table and related two tables by month columns.
Calculated column:
MonthNo = RELATED('Month'[MonthNo])
Moving Month Average (3M) =
AVERAGEX (
FILTER (
'Table',
'Table'[MonthNo] <= EARLIER ( 'Table'[MonthNo] )
&& 'Table'[MonthNo]
>= EARLIER ( 'Table'[MonthNo] ) - 2
&& 'Table'[Part ID] = EARLIER ( 'Table'[Part ID] )
),
'Table'[Sales]
)
Result is as below.
You can download the pbix file from this link: Moving monthly average by Item
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
My sample table:
I build a Month table and related two tables by month columns.
Calculated column:
MonthNo = RELATED('Month'[MonthNo])
Moving Month Average (3M) =
AVERAGEX (
FILTER (
'Table',
'Table'[MonthNo] <= EARLIER ( 'Table'[MonthNo] )
&& 'Table'[MonthNo]
>= EARLIER ( 'Table'[MonthNo] ) - 2
&& 'Table'[Part ID] = EARLIER ( 'Table'[Part ID] )
),
'Table'[Sales]
)
Result is as below.
You can download the pbix file from this link: Moving monthly average by Item
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I havent tested it yet, but it looks like exactly what I was looking for. Thank you!
Hi Amit
It does work for a measure but not for a calculated column
@Anonymous , Try like this example with date table
Rolling 3 = divide( CALCULATE(sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-3,MONTH)) ,
CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,MONTH), not(isblank((Sales[Sales])))))
or
CALCULATE(Average(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-12,MONTH))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |