Previous Week

[schwinnen]

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.  

[Greg_Deckler · Accepted solution]

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

[schwinnen]

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

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

[schwinnen]

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

[schwinnen]

@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.  

[ryan_mayu]

@schwinnen

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)