Using Sequential Week Numbers to create Last Week and Previous Week Measures

[Anonymous]

I have created a Calculated Column for Sequential Week Number using the following DAX:

Week - Sequential Number = IF('Calendar'[Week - Year]=2019, 'Calendar'[Week - Number], 'Calendar'[Week - Number]+52)

Primarily to deal with the problematic Last Week calculations around the change of the new year. This results in the following values around that time:

 

What I would like to do is reference this Sequential Number when creating a measure called LW - Total Quotes. I currently have a measure that is [Total - Quotes] as a distinct count measure, so theoretically the new measure is "[Total Quotes] during ([Week - Sequential Number]-1)"

I've tried various functions with DATESBETWEEN, FILTER, CALCULATE, etc. and I think I'm just missing the fundamental concept of how to specify what sequential date TODAY() is in. I know typically we would use WEEKNUM, but this obviously does not account for the new year Week Number reset.

Please let me know what I'm missing!

[PaulDBrown · Accepted solution]

@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 

[PaulDBrown · Accepted solution]

@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 

[Anonymous]

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

[PaulDBrown]

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

[Anonymous · Accepted solution]

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!

[PaulDBrown]

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!)