Forum Discussion

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