Week Is Not So Weak: WTD, Last WTD, and This Week vs Last Week

[amitchandak]

Objective: We would like to have WTD(Week Till Date) Last WTD and This Week vs  Last Week.

Dataset: We have taken Sales data. The data is from April 2018 till April 2020. We have created a date Calendar. 

Steps: We create a Date Calendar With Monday to Sunday Week, with the following week-related columns.

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date] & " to "& [Week End date]
Weekday = WEEKDAY([Date],2)
WeekDay Name = FORMAT([Date],"ddd")

Marked Date Table as a Date table.

Marked Week Start Date and Sort column for Week Name

Created relation between Date Table and Order Table

Now, to have Week Vs Last Week, we will use Week Rank, and the way to go is in the filter in the manner shown below:

FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])).

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

For WTD, we will also use Weekday.

WTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[Weekday] <=max('Date'[Weekday])))
LWTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -1) && 'Date'[Weekday] <=max('Date'[Weekday])))

Pbix is attached to this blog.

My Previous Blogs - Date Difference Across Table- Direct Query Mode, Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard Time Periods and Comparing Data Across Date Ranges
Connect on LinkedIn

WOW_WTD.pbix

[amitchandak]

Some of you has asked about last year. Few Options you can try.

LYWTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52) && 'Date'[Weekday] <=max('Date'[Weekday])))

LYWTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=(max('Date'[Year]) -1)
 && 'Date'[Week Number]=(max('Date'[Week Number]))
 && 'Date'[Weekday] <=max('Date'[Weekday])))

[amitchandak]

Last year same week and Last year Week

Last year same Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

Last year Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=(max('Date'[Year]) -1)
 && 'Date'[Week Number]=(max('Date'[Week Number]))
))

[Anonymous]

Is there a way to show Latest 4, 12, 52 and YTD time frames as a dropdown in the report view so the user can select from it ? I have the same calendar date table. 

Thanks! 

[Anonymous]

tks

[danialabrar]

Hi, great post, really helped me. Is there a way for the Week Name to be in dd/mm/yy format instead of mm/dd/yy? @amitchandak 

[HH-HELD]

Hi @amitchandak

I tried the "Week Start Date" function but got the error message below. My "date" dataset looks fine. Would you please advise what I need to do to proceed? Thank you very much in advance!

ERROR MESSAGE: "A single value for column in table cannot be determined..."