Rolling Avg for Measured Value 7 and 14 Day

[ddave84]

Hello,  

I have some shipping data and I'm trying to roll up the 7 and 14 day rolling avg for a measured value.  I have created a separate calendar table.  Also, I have created many one to many relationship for the calendar table to the shipping order date.  To get my cycle times I take the Avg of each week then get the ratio based off those values.  I have reviewed the previous threads and tried their solutions, but I keep getting either null or invalid data.  I have tried to attach the .pbix but for some reason it will not let me.  Thank you 

[AmiraBedh]

Can you please share your pbix file or your dataset it is better than sharing an image?

[ddave84]

https://www.dropbox.com/scl/fi/5rdtcvq4pr822c561yo72/Dim_Date_Template.pbix?rlkey=3efmav31bc9rt6r0lt... 

https://www.dropbox.com/scl/fi/514n9reifkwwxuwlfw686/Shipping.csv?rlkey=domvlcmn355kxxvk3o6h38ovm&dl... 

[AmiraBedh · Accepted solution]

I made some improvements to your calendar table : 

CalTable = 

ADDCOLUMNS (
    CALENDAR (DATE(2020,1,1), DATE(2025,12,31)), -- Adjust these dates to your needs
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMMM"),
    "MonthNo", MONTH([Date]),
    "DOW", WEEKDAY([Date]), 
    "Quarter", "Q" & QUARTER([Date]),
    "QuarterNumber", QUARTER([Date]),
    "WorkWeek", 
    VAR CorrectYear = 
        IF(
            MONTH([Date]) = 12 && WEEKNUM([Date],2) = 1,
            YEAR([Date]) + 1,
            IF(
                MONTH([Date]) = 1 && WEEKNUM([Date],2) > 50,
                YEAR([Date]) - 1,
                YEAR([Date])
            )
        )
    VAR IsoWeek = 
        IF(
            MONTH([Date]) = 12 && WEEKNUM([Date],2) = 1,
            1,
            IF(
                MONTH([Date]) = 1 && WEEKNUM([Date],2) > 50,
                WEEKNUM(DATE(YEAR([Date])-1, 12, 31),2),
                WEEKNUM([Date],2)
            )
        )
    RETURN
        FORMAT(CorrectYear, "0000") & FORMAT(IsoWeek, "00")
)

the relationship was between a datetime and date column (Shipping and CalTable)

so I created this column in the fact table :

OrderDateFormatted = DATEVALUE(Shipping[Order Date])

and made the join like below : 

Also for the Workweek the column was displaying values like 202027 (here the 27th week is concatenated to the year 2020) but in some cases you had 20201 which is in reality 2020 concatenated to the 1st week so I made the adjusement for the weeks between [1-9] to include the zero.

Now for the measures : 

Shipping Order Ratio = 
DIVIDE(
    AVERAGE('Shipping'[Ordery Cycle Time_Hrs]),
    AVERAGE('Shipping'[Fullfilment Cycle Time_Hrs])
)
____________________________
7 Day Rolling Avg = 
VAR CurrentDate = MAX('CalTable'[Date])

RETURN
IF(
    COUNTROWS(FILTER('Shipping', 'Shipping'[OrderDateFormatted] <= CurrentDate)) > 0,
    AVERAGEX(
        FILTER(
            ALL('Shipping'),
            COUNTROWS(
                FILTER(
                    'CalTable',
                    'CalTable'[Date] <= CurrentDate && 
                    'CalTable'[Date] > CurrentDate - 7
                )
            ) > 0
        ),
        [Shipping Order Ratio]
    ),
    BLANK()
)
_____________________________________________
14-day Rolling Avg = 
AVERAGEX(
    DATESINPERIOD(
        'CalTable'[Date],
        LASTDATE('CalTable'[Date]),
        -14,
        DAY
    ),
    [Shipping Order Ratio]
)

Find attached the new version of your pbix

[ddave84]

I just verified the CalTable and I noticed the workweek are off by N+1.  Would this make any difference?  The reason I ask is that I'm experimenting with calculating the previous 1 and 4 weeks of data.   

[AmiraBedh]

Can you please define the expected output for your dim calendar ?

[ddave84]

Thank you for the help and guidance.  

[AmiraBedh]

welcome 🙂