Forum Discussion
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: ...
You should be able to use a veriation on my "Sequential" Quick Measure:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231
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?
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 myNumAwesome, 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