Forum Discussion

sgsukumaran's avatar
sgsukumaran
Resolver II
8 years ago
Solved

Rolling weeks calculation

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", 1...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

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

     

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

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's avatar
    sgsukumaran
    Resolver II
    8 years ago

    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's avatar
      Greg_Deckler
      Community Champion
      8 years ago

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