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