Forum Discussion
jluc311
2 years agoRegular Visitor
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 nu...
- 2 years ago
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] ) )
jluc311
2 years agoRegular Visitor
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 |
- Ashish_Mathur2 years agoSuper User
Without a Date column in the Fact Tables and a proper Calendar Table, I will not be able to help.