Rolling weeks calculation

[sgsukumaran]

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.

[Greg_Deckler]

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

[sgsukumaran]

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

[Greg_Deckler]

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

[Greg_Deckler]

I posted a better/more complete version here:

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

[sgsukumaran]

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

[Greg_Deckler]

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...

[sgsukumaran]

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

[Greg_Deckler]

So, is it as simple as:

WeekEnding, Sales

In a single table or ?

[sgsukumaran]

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

[Greg_Deckler]

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?

[sgsukumaran]

@Greg_Deckler

Yes. Thats exactly it.

[Greg_Deckler · Accepted solution]

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