Rolling Average using Ship Day

[jluc311]

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

[jluc311]

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

[Ashish_Mathur]

Hi,

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

[jluc311 · 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] ) )

[jluc311]

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

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

[Ashish_Mathur]

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.