Forum Discussion
Using Sequential Week Numbers to create Last Week and Previous Week Measures
Anonymous
I'm not quite following you.
What is the expected result? (can you show a sample table with the data you are expecting?)
From your screenshot, you seem to missing a YearWeek column (to be able to do calculations pertaining to years). To add a YearWeek column use:
YearWeek = YEAR(Calendar Table[Date]) * 100 + WEEKNUM(Calendar Table[Date])With this, you can do calculations involving current week where current week is (use in measure):
Current YearWeek = YEAR(TODAY()) *100 + WEEKNUM (TODAY())You can then create a YearWeek Index in a calculated column to use in calculations:
YearWeek Index = RANX(Calendar, Calendar [YearWeek],,ASC, Dense)(I prefer to do the rank DESC since you can then establish that "Todays" week is 1, last week is 2...)
Check out this thread to see how it works in practice to calculate the previous 8 weeks amounts based on a slicer selection:
https://community.powerbi.com/t5/Desktop/Last-8-Weeks-Sales-spanning-previous-year/m-p/951682#M456017I solved it using:
Week - Current Seq = VAR MaxDate = MAX(Quotes[Quote - Creation Date]) VAR WeekNum = IF(WEEKNUM(MaxDate,1) = 53, 1, WEEKNUM(MaxDate,1)) RETURN IF( IF(WeekNum = 1 && MONTH(MaxDate) = 12, YEAR(MaxDate)+1, YEAR(MaxDate))=2019, WeekNum, WeekNum+52 )Thanks again Paul, you definitely got my brain on the right path!
Anonymous
I'm not quite following you.
What is the expected result? (can you show a sample table with the data you are expecting?)
From your screenshot, you seem to missing a YearWeek column (to be able to do calculations pertaining to years). To add a YearWeek column use:
YearWeek = YEAR(Calendar Table[Date]) * 100 + WEEKNUM(Calendar Table[Date])With this, you can do calculations involving current week where current week is (use in measure):
Current YearWeek = YEAR(TODAY()) *100 + WEEKNUM (TODAY())You can then create a YearWeek Index in a calculated column to use in calculations:
YearWeek Index = RANX(Calendar, Calendar [YearWeek],,ASC, Dense)(I prefer to do the rank DESC since you can then establish that "Todays" week is 1, last week is 2...)
Check out this thread to see how it works in practice to calculate the previous 8 weeks amounts based on a slicer selection:
https://community.powerbi.com/t5/Desktop/Last-8-Weeks-Sales-spanning-previous-year/m-p/951682#M456017
Hello Paul,
I suppose the root of my problem is I don't know how to reference a calculated column in the row for TODAY() or DATE(). Is it possible to use the sequential week of TODAY() as a variable in a measure?
Something Like:
'Calendar'[Week Number]= WEEKNUM(TODAY(),2) - 1)but where I can use my sequential week number instead of the WEEKNUM() built in date formula.
I think if I can get an answer to that, then I can work my way through the rest of it!
Thanks,
spham
Anonymous
The problem with working with the Week Number is that it does not discriminate year. So WEEKNUM(TODAY()) is 9, but so is the same week last year and the year before that etc...
So you need to create a column in your Calendar which sets the year context (YearWeek): see my previous post to see if it helps.
BTW when you talk about "how to reference a calculated column in the row for TODAY() or DATE()", I'm not sure I understand. Do you want a column in your calendar with the WEEKNUM value for TODAY's date?
If you need to reference calculations to the current date, just use the approriate expression in the filter expression of your measures. (see my previous post)
Here is an example of the kind of Calendar table I would use as my template:
If you a re still getting stuck, please scramble up a mock table in Excel with dummy data to illustrate what you need.
I solved it using:
Week - Current Seq = VAR MaxDate = MAX(Quotes[Quote - Creation Date]) VAR WeekNum = IF(WEEKNUM(MaxDate,1) = 53, 1, WEEKNUM(MaxDate,1)) RETURN IF( IF(WeekNum = 1 && MONTH(MaxDate) = 12, YEAR(MaxDate)+1, YEAR(MaxDate))=2019, WeekNum, WeekNum+52 )Thanks again Paul, you definitely got my brain on the right path!
- I’m glad you worked it out! And thanks for including my suggestion as part of the solution.
(Though I must confess, seeing your solution, I’m not too sure how I actually helped you!)