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
Super User
2 years ago

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.