Forum Discussion

jluc311's avatar
jluc311
Regular Visitor
2 years ago
Solved

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...
Need Help
  • jluc311's avatar
    jluc311
    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] ) )
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
2 years ago

Hi,

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

jluc311's avatar
jluc311
Regular Visitor
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] ) )