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", 11/05/2017, 10/29/2017, 10/22/2017.

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

     

     

12 Replies

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