Forum Discussion
4 / 13/ 52 Week Rolling Averages
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
7 Replies
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
Community Champion
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.
I attached the sample file via link in my original post
- AlB
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.
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
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.
