Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
jluc311
Regular 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 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
1 ACCEPTED SOLUTION

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] ) )

View solution in original post

6 REPLIES 6
jluc311
Regular Visitor

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-Day...

Hi,

I do not have that file.  Furthermore, as i have mentioned, i will need a Date Table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Regular 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



Without a Date column in the Fact Tables and a proper Calendar Table, I will not be able to help.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors