cancel
Showing results for 
Search instead for 
Did you mean: 

Rolling Weeks

Super User
1339 Views
Super User
Super User

Rolling Weeks

Similar to Rolling Months, https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Months/m-p/391499

 

it is often handy to have the ability to do rolling weeks.

 

For this to work, previous Quick Measures of Week Starting and Week Ending are required. https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Starting/td-p/391487

 

The attached file contains all of the necessary measures, Week Starting, Week Ending, the Rolling Week Start and Rolling Week End measures and finally a measure that demonstrates how to use the dates returned from Rolling Week Start and Rolling Week End.

 

Rolling Week End

 

Rolling Week End = 
VAR DateFrom = MAX([Date])
VAR WeeksBack = 0
VAR tmpCalendar = CALCULATETABLE('Calendar',ALL('Calendar'))
VAR tmpCalendar1 = ADDCOLUMNS(tmpCalendar,"WeekNum",WEEKNUM([Date]),"WeekEnding",[mWeekEnding],"WeekStarting",[mWeekStarting])
VAR LookupDate = DateFrom-7*WeeksBack
VAR tmpCalendar2 = FILTER(tmpCalendar1,[Date]=LookupDate)
RETURN MAXX(tmpCalendar2,[WeekEnding])

 

Rolling Week Start

 

Rolling Week Start = 
VAR DateFrom = MAX([Date])
VAR WeeksBack = 4
VAR tmpCalendar = CALCULATETABLE('Calendar',ALL('Calendar'))
VAR tmpCalendar1 = ADDCOLUMNS(tmpCalendar,"WeekNum",WEEKNUM([Date]),"WeekEnding",[mWeekEnding],"WeekStarting",[mWeekStarting])
VAR LookupDate = DateFrom-7*(WeeksBack-1)
VAR tmpCalendar2 = FILTER(tmpCalendar1,[Date]=LookupDate)
VAR retValue = MAXX(tmpCalendar2,[WeekStarting])
RETURN IF(ISBLANK(retValue),MINX(tmpCalendar1,[WeekStarting]),retValue)

 

Measure (calculates a value using Rolling Weeks)

 

Measure = 
VAR rollweekstart = [Rolling Week Start]
VAR rollweekend = [Rolling Week End]
VAR tmpTable = ALL('Calendar')
VAR tmpTable1 = FILTER(tmpTable,[Date]>=rollweekstart&&[Date]<=rollweekend)
RETURN AVERAGEX(tmpTable1,[Value])

 

 

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!