Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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))
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |