Rolling Weeks 07-11-2021 16:39 PM

[Greg_Deckler]

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

eyJrIjoiMTUzZTYxMzItOTkxZC00ZjE2LThjM2EtYzA0NGE2YjM2Mzk2IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

[anuvarghes]

@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?