topic 4 / 13/ 52 Week Rolling Averages in DAX Commands and Tips

4 / 13/ 52 Week Rolling Averages

Quest77 — Sat, 24 Dec 2022 17:55:17 GMT

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

Re: 4 / 13/ 52 Week Rolling Averages

AlB — Sat, 24 Dec 2022 17:35:37 GMT

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.

Re: 4 / 13/ 52 Week Rolling Averages

Quest77 — Sat, 24 Dec 2022 17:55:42 GMT

I attached the sample file via link in my original post

Re: 4 / 13/ 52 Week Rolling Averages

AlB — Sat, 24 Dec 2022 20:10:54 GMT

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.

Re: 4 / 13/ 52 Week Rolling Averages

AlB — Sat, 24 Dec 2022 20:12:32 GMT

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.

Re: 4 / 13/ 52 Week Rolling Averages

AlB — Sat, 24 Dec 2022 20:14:20 GMT

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.

Re: 4 / 13/ 52 Week Rolling Averages

stellasun820 — Thu, 28 Mar 2024 13:22:13 GMT

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.

Re: 4 / 13/ 52 Week Rolling Averages

stellasun820 — Thu, 28 Mar 2024 13:28:09 GMT

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.