4 / 13/ 52 Week Rolling Averages

[Quest77]

Hi I am trying to create a number of cards that contain the following rolling averages. 4 week, 13 week and 52 week. I don't know what measure to create in order to achieve these reults. I can try and send the sample file but not sure best way to attach it. 

Link to sample file attached

https://1drv.ms/u/s!AiZ1-kl9lagZgt4olcKdIRRuzIxiEg?e=bbrnvL

Data Table

Calendar Table

Visual I am trying to achieve for weekly averages

Weekly Sum Matrix already built

[AlB · Accepted solution]

@Quest77 

See it all at work in the attached file (Overview page)

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[stellasun820]

When i used the same function to create measure current week offset, it got failed due to an error that a single value cannot be determined. Anyway i can connect with you about this problem? Thank you very much.

[AlB]

@Quest77 

Exactly same pattern for the 13 and 52 week. Only the variable at the top changes 

13 Week Rolling Average = 
VAR numWeeks_ = 13
VAR weeksT_ =
    CALCULATETABLE (
        DISTINCT ( 'Calendar'[CurrentWeekOffset] ),
        'Calendar'[CurrentWeekOffset] >= - numWeeks_,
        'Calendar'[CurrentWeekOffset] <= 0,
        ALL ( 'Calendar' )
    )
RETURN
    CALCULATE (
        AVERAGEX ( weeksT_, CALCULATE ( SUM ( SETTLEMENT_STATEMENTS[Sales] ) ) ),
        ALL ( 'Calendar' )
    )

52 Week Rolling Average = 
VAR numWeeks_ = 52
VAR weeksT_ =
    CALCULATETABLE (
        DISTINCT ( 'Calendar'[CurrentWeekOffset] ),
        'Calendar'[CurrentWeekOffset] >= - numWeeks_,
        'Calendar'[CurrentWeekOffset] <= 0,
        ALL ( 'Calendar' )
    )
RETURN
    CALCULATE (
        AVERAGEX ( weeksT_, CALCULATE ( SUM ( SETTLEMENT_STATEMENTS[Sales] ) ) ),
        ALL ( 'Calendar' )
    )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB]

@Quest77 

First create a calculated column with the number of weeks offset from the current week for each date in the Calendar table. This will simplify the measures:

CurrentWeekOffset = 
VAR currentWeekStart_ =
    DATEVALUE (
        LOOKUPVALUE ( 'Calendar'[Week Start Date], 'Calendar'[Date], TODAY () )
    )
VAR currentRowWeekStart_ = 'Calendar'[Week Start Date]
RETURN
    DATEDIFF ( currentWeekStart_, currentRowWeekStart_, WEEK )

Then build the measure for the 4 week rolling average. Note it is going back from the current date

4 Week Rolling Average V2 = 
VAR numWeeks_ = 4
VAR weeksT_ =
    CALCULATETABLE (
        DISTINCT ( 'Calendar'[CurrentWeekOffset] ),
        'Calendar'[CurrentWeekOffset] >= - numWeeks_,
        'Calendar'[CurrentWeekOffset] <= 0,
        ALL ( 'Calendar' )
    )
RETURN
    CALCULATE (
        AVERAGEX ( weeksT_, CALCULATE ( SUM ( SETTLEMENT_STATEMENTS[Sales] ) ) ),
        ALL ( 'Calendar' )
    )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[stellasun820]

If my date table already has week start date, do we still need calendarweekoffset? When i tried to create the first measure, it didn't work.

[AlB]

Hi @Quest77 

You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Quest77]

I attached the sample file via link in my original post