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])
This is exactly what i am trying to create. So if Select 3/10 /2018 from my slicer i would wanted the rolling measures to display all weeks before it
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