Forum Discussion
Rolling Average using Ship Day
Hi, so I've spent a few days on this one and I'm just missing something. I need to apply a rolling average on the amount. There's slicers for Brand, Channel, SubYear and a Smoothing slider for the number of days for which we'll take the rolling average. The goal is to have these on a line graph for each SubYear that's chosen. ShipDay on the x-axis, RollingAverage as the y-axis. Consider the data below. This isn't a usual Rolling Average calculation utilizing a date field. I can relate this table using ShipDay to a Calendar table that provides the actual date, but it's not really necessary (I don't think). Important to know there's many more Channels, and SubYear variations. This is just a sample Any help would be much appreciated.
ShipDay | Brand | Channel | SubYear | Amount |
1 | ABC123 | WEST | 2024SL | 25000 |
1 | ABC123 | WEST | 2024BP | 23000 |
1 | ABC123 | CENTRAL | 2024SL | 19000 |
1 | ABC123 | CENTRAL | 2024BP | 20000 |
1 | DEF456 | WEST | 2024SL | 10500 |
1 | DEF456 | WEST | 2024BP | 12000 |
2 | ABC123 | WEST | 2024SL | 23000 |
2 | ABC123 | WEST | 2024BP | 23000 |
2 | ABC123 | CENTRAL | 2024SL | 19000 |
2 | ABC123 | CENTRAL | 2024BP | 20000 |
2 | DEF456 | WEST | 2024SL | 11230 |
2 | DEF456 | WEST | 2024BP | 11200 |
3 | ABC123 | WEST | 2024SL | 20000 |
3 | ABC123 | WEST | 2024BP | 22000 |
3 | ABC123 | CENTRAL | 2024SL | 14000 |
3 | ABC123 | CENTRAL | 2024BP | 15000 |
3 | DEF456 | WEST | 2024SL | 10050 |
3 | DEF456 | WEST | 2024BP | 12000 |
4 | ABC123 | WEST | 2024SL | 25000 |
4 | ABC123 | WEST | 2024BP | 26000 |
4 | ABC123 | CENTRAL | 2024SL | 12000 |
4 | ABC123 | CENTRAL | 2024BP | 13000 |
4 | DEF456 | WEST | 2024SL | 10000 |
4 | DEF456 | WEST | 2024BP | 10010 |
I wound up getting the rolling average to work with as you said a date table.
For those that may see this, here is a measure that pulls in a smoothing parameter while also using shipping days not calendar days.Rolling Avg = VAR SmoothingValue = Smoothing[Parameter Value] VAR EndDate = MAX('Calendar'[ACCOUNTING_DATE]) VAR ValidDates = FILTER ( ALL ( 'Calendar' ), 'Calendar'[IsShipDay] = "Y" && 'Calendar'[ACCOUNTING_DATE] <= EndDate ) VAR RankedDates = ADDCOLUMNS( ValidDates, "Rank", RANKX(ValidDates, 'Calendar'[ACCOUNTING_DATE], , DESC, Dense) ) VAR FilteredDates = FILTER( RankedDates, [Rank] <= SmoothingValue ) RETURN AVERAGEX ( FilteredDates, CALCULATE ( SUM ( 'SalesData'[AMOUNT] ) )
6 Replies
Hi,
Having a Calendar Tabe with a relationship (Many to one and Single) to your Data Table would make to problem easier to solve. So share the download link of the PBi file and show the expected result in a simple Table format.
Appreciate you taking a look.
PBIX file
In the sample, there should be multiple lines on the chart. Seems like the Rolling Average measue is ignoring the context and consolidating everything. RollingAvg would kick in starting Day 3 since the Smoothing Parameter for Days is 3 Expected result would be like so:ShipDay
Brand
Channel
SubYear
Amount
RollingAvg (3 Day)
1
ABC123
WEST
2024SL
25000
25000
1
ABC123
WEST
2024BP
23000
23000
1
ABC123
CENTRAL
2024SL
19000
19000
1
ABC123
CENTRAL
2024BP
20000
20000
1
DEF456
WEST
2024SL
10500
10500
1
DEF456
WEST
2024BP
12000
12000
2
ABC123
WEST
2024SL
23000
23000
2
ABC123
WEST
2024BP
23000
23000
2
ABC123
CENTRAL
2024SL
19000
19000
2
ABC123
CENTRAL
2024BP
20000
20000
2
DEF456
WEST
2024SL
11230
11230
2
DEF456
WEST
2024BP
11200
11200
3
ABC123
WEST
2024SL
20000
22666.67
3
ABC123
WEST
2024BP
22000
22666.67
3
ABC123
CENTRAL
2024SL
14000
17333.33
3
ABC123
CENTRAL
2024BP
15000
18333.33
3
DEF456
WEST
2024SL
10050
10593.33
3
DEF456
WEST
2024BP
12000
11733.33
4
ABC123
WEST
2024SL
25000
22666.67
4
ABC123
WEST
2024BP
26000
23666.67
4
ABC123
CENTRAL
2024SL
12000
15000
4
ABC123
CENTRAL
2024BP
13000
16000
4
DEF456
WEST
2024SL
10000
10426.67
4
DEF456
WEST
2024BP
10010
11070
Without a Date column in the Fact Tables and a proper Calendar Table, I will not be able to help.
Hi Ashish, you've seemed to have solved a similar issue. Could you provide the PBI file again?
https://community.fabric.microsoft.com/t5/Desktop/Rolling-Average-Measure-with-Parameter-Trading-Days-Only/td-p/2553775Hi,
I do not have that file. Furthermore, as i have mentioned, i will need a Date Table.
I wound up getting the rolling average to work with as you said a date table.
For those that may see this, here is a measure that pulls in a smoothing parameter while also using shipping days not calendar days.Rolling Avg = VAR SmoothingValue = Smoothing[Parameter Value] VAR EndDate = MAX('Calendar'[ACCOUNTING_DATE]) VAR ValidDates = FILTER ( ALL ( 'Calendar' ), 'Calendar'[IsShipDay] = "Y" && 'Calendar'[ACCOUNTING_DATE] <= EndDate ) VAR RankedDates = ADDCOLUMNS( ValidDates, "Rank", RANKX(ValidDates, 'Calendar'[ACCOUNTING_DATE], , DESC, Dense) ) VAR FilteredDates = FILTER( RankedDates, [Rank] <= SmoothingValue ) RETURN AVERAGEX ( FilteredDates, CALCULATE ( SUM ( 'SalesData'[AMOUNT] ) )
