Forum Discussion
Rolling weeks calculation
OK, see attached PBIX Page 2. I added a Sales table and created a "Rolling Sales" measure. And graphed it.
Rolling Sales = VAR rollweekstart = [Rolling Week Start] VAR rollweekend = [Rolling Week End] VAR tmpTable = ALL('Sales') VAR tmpTable1 = FILTER(tmpTable,[Date]>=rollweekstart&&[Date]<=rollweekend) RETURN SUMX(tmpTable1,[Value])
OK, you need slight variations of my Week Start and Week End measures plus the ones contained in the attached file and listed below.
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
VAR tmpCalendar2 = FILTER(tmpCalendar1,[Date]=LookupDate)
VAR retValue = MAXX(tmpCalendar2,[WeekStarting])
RETURN IF(ISBLANK(retValue),MINX(tmpCalendar1,[WeekStarting]),retValue)
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])I posted a better/more complete version here:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Weeks/m-p/391694#M128
Thanks. Where would I add the sum of sales in this mix?
That would be the Measure formula that I posted in the Quick Measure: https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Weeks/m-p/391694
So, something along the lines of:
Measure = VAR rollweekstart = [Rolling Week Start] VAR rollweekend = [Rolling Week End] VAR tmpTable = ALL('Calendar') VAR tmpTable1 = FILTER(tmpTable,[Date]>=rollweekstart&&[Date]<=rollweekend) RETURN SUMX(tmpTable1,[Value])If you could give me a sense of your source data, that would greatly help. See this post here: Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Thanks. This is a direct query from tabular model. so I am limited with options.
