Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

amitchandak

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

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. 

Screenshot 2020-04-27 19.03.07.png

 

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

 

 

Screenshot 2020-04-27 19.02.59.png

 

Marked Date Table as a Date table.

Marked Week Start Date and Sort column for Week Name

Screenshot 2020-04-27 19.02.46.png

 

Created relation between Date Table and Order Table

Screenshot 2020-04-27 19.02.26.png

 

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

 

 

 

Screenshot 2020-04-27 12.49.50.png

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

 

 

 

Screenshot 2020-04-27 19.02.10.png

 

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

Comments

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

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

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 

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