Rolling Weeks

Greg_Deckler — Wed, 27 Jun 2018 17:41:38 GMT

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])

eyJrIjoiMTUzZTYxMzItOTkxZC00ZjE2LThjM2EtYzA0NGE2YjM2Mzk2IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Re: Rolling Weeks

anuvarghes — Sun, 11 Jul 2021 23:39:44 GMT

Greg_Deckler Would you be able to help with how the DAX would change if there were duplicate dates...essentially would need to sum by each week and then average?

topic Rolling Weeks in Quick Measures Gallery

Rolling Weeks

Re: Rolling Weeks