topic Re: Rolling weeks calculation in Desktop

Rolling weeks calculation

sgsukumaran — Sat, 07 Apr 2018 19:46:17 GMT

I am tring to calculate rolling 4 weeks based on selected date from slicer. The slicer has only week ending date. So If I select "11/19/2017), I would I ideally want to see sales for "11/12/2017", 11/05/2017, 10/29/2017, 10/22/2017.

Re: Rolling weeks calculation

Greg_Deckler — Sat, 07 Apr 2018 20:05:19 GMT

Take a look at my Week Starting/Ending measures in the Quick Measures Gallery. Should get you what you need to filter your data correctly. If not, I can make the modifications to do this most likely.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Starting/m-p/391487

Sample data would help me come up with a specific solution. 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

Re: Rolling weeks calculation

sgsukumaran — Sat, 07 Apr 2018 20:16:59 GMT

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

Re: Rolling weeks calculation

Greg_Deckler — Sat, 07 Apr 2018 21:15:44 GMT

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

Re: Rolling weeks calculation

Greg_Deckler — Sat, 07 Apr 2018 22:02:46 GMT

I posted a better/more complete version here:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Weeks/m-p/391694#M128

Re: Rolling weeks calculation

sgsukumaran — Sat, 07 Apr 2018 23:22:16 GMT

Thanks. Where would I add the sum of sales in this mix?

Re: Rolling weeks calculation

Greg_Deckler — Sun, 08 Apr 2018 02:06:32 GMT

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

Re: Rolling weeks calculation

sgsukumaran — Sun, 08 Apr 2018 02:47:18 GMT

Thanks. This is a direct query from tabular model. so I am limited with options.

Re: Rolling weeks calculation

Greg_Deckler — Sun, 08 Apr 2018 14:55:51 GMT

So, is it as simple as:

WeekEnding, Sales

In a single table or ?

Re: Rolling weeks calculation

sgsukumaran — Sun, 08 Apr 2018 17:51:35 GMT

Greg_Deckler

Yes All i would need is Sum(sales) for rolling 4 weeks. The Strtdate is from Fiscalcalendar and Sales is from Fact joined by calendar date.

Re: Rolling weeks calculation

Greg_Deckler — Sun, 08 Apr 2018 18:30:12 GMT

OK, I really need sample data so that I can get this right. What does your fiscal calendar table look like? Is it just a standard calendar table like that generated by CALENDAR() function?

And then I assume that your sales table would have something in it like:

Sales table

Date,Sales

4/1/2018,1000

4/1/2018,500

4/2/2018,300

4/2/2018,100

etc.

And you have a relationship like:

Calendar 1->*Sales

Is this all correct?

Re: Rolling weeks calculation

sgsukumaran — Sun, 08 Apr 2018 20:11:24 GMT

Greg_Deckler

Yes. Thats exactly it.

Re: Rolling weeks calculation

Greg_Deckler — Sun, 08 Apr 2018 20:24:24 GMT

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