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: ...
Need Help
  • 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
      Icon for Community Champion rankCommunity 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
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Awesome, Greg_Deckler.  That works, with one small change.  I still have to create a formula for Current Week.  Then, use that in the last line instead of weeknum.

         

        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,[Current Week],MyStart+[Current Week])
RETURN myNum