Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
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. |
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. |
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.
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. |
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.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
127 | |
30 | |
27 | |
24 | |
22 |