cancel
Showing results 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.

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.

##### WEST
###### 10010
1 ACCEPTED SOLUTION
Regular Visitor

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 =
ValidDates,
"Rank", RANKX(ValidDates, 'Calendar'[ACCOUNTING_DATE], , DESC, Dense)
)
VAR FilteredDates =
FILTER(
RankedDates,
[Rank] <= SmoothingValue
)
RETURN
AVERAGEX (
FilteredDates,
CALCULATE ( SUM ( 'SalesData'[AMOUNT] ) )``````
6 REPLIES 6
Regular Visitor

Hi Ashish, you've seemed to have solved a similar issue. Could you provide the PBI file again?

Super User

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

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 =
ValidDates,
"Rank", RANKX(ValidDates, 'Calendar'[ACCOUNTING_DATE], , DESC, Dense)
)
VAR FilteredDates =
FILTER(
RankedDates,
[Rank] <= SmoothingValue
)
RETURN
AVERAGEX (
FilteredDates,
CALCULATE ( SUM ( 'SalesData'[AMOUNT] ) )``````
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:

##### WEST
###### 11070

Super User

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

Announcements

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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors