Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Previous Week

I have a report where I just look at the previous week.  I had a pretty simple way to do this that worked fine until we hit a new year.  I had simply used Week Number and then created these columns:

CURRENT WEEK = WEEKNUM(TODAY())

and

WEEK RELATIVE TO CURRENT = QA[CURRENT WEEK]-QA[WEEK NUMBER]

Then I just filterd on WEEK RELATIVE TO CURRENT is "1" and that would give me everything for the previous week.  However, not that we are in a new year, this does not work becuase the current week is week 1 and last week was week 52.  I should have thought of this before now, but everything was working fine so it didn't cross my mind.  

Is there a better way to do previous week?  Also, a way to do previous weeks?  Despite the obviously flaw in my method, one good thing was that I could go back mulitple weeks.  If I wanted to look at the previous 4 weeks, I would just filter on WEEK RELATIVE TO CURRENT is 1, 2, 3 or 4.  

Need Help

6 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    7 years ago

    Anonymous

     

    What about using dateadd formula to do this? I think it's better to use filter combination year and week number.

     

    7daysago = DATEADD('date'[Date],-7,DAY)

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Greg_Deckler, I believe that will work.  I will try it and let you know.  I was working on a similar solution by simply adding 53 to any week in 2019.  My approach has some obvious flaws.  

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Greg_Deckler, I do have one question.  Your sequential method worked beautifully for part one, which would be to get, or example, week one of 2019 to be week number 54.  However, what would you recommend for calculating current week, given that I still need to create a calculation for week relative to the current week?

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        7 years ago

        I believe what you want to do for the second part would be to have this formula variation for your "current" week:

         

        Column = 
VAR MaxWeeks = SUMMARIZE(ALL('YearWeeks'),'YearWeeks'[Year],"MaxWeek",MAX('YearWeeks'[WeekNum]))
VAR MyYear = YEAR(TODAY())
VAR MyStart = SUMX(FILTER(MaxWeeks,[Year]<MyYear),[MaxWeek])
VAR firstYear = CALCULATE(FIRSTNONBLANK('YearWeeks'[Year],1),ALL('YearWeeks'))
VAR myNum = IF(MyYear=firstYear,[WeekNum],MyStart+[WeekNum])
RETURN myNum