Power BI Community Blog

2 MIN READ

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

amitchandak

Super User

6 years ago

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

Updated 6 years ago

Version 2.0

Working with Non Standatd Periods

amitchandak

Super User

Joined June 27, 2019

View Profile

Power BI Community Blog

On this blog you will find articles, guides and information created by our community members and, from time to time, roundups and community news. If you are interested in becoming a community blog author, send a private message to Natalie_iTalent. An overview of the community blogs can be found here.

9 Comments

amitchandak

Super User

6 years ago

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

Super User

6 years ago

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
Not applicable
5 years ago
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
Not applicable
4 years ago
tks
Anonymous
Not applicable
3 years ago
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
Regular Visitor
3 years ago
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..."
pcook
Regular Visitor
1 year ago
Hi @amitchandak -
I am building a matrix using your Last Week and This Week measures and they are extremely useful (just like everything else you post!). But I need to add another value to the filter, and I'm experiencing an error.
Can you help?
Existing measure: Resolutions Week = CALCULATE(SUM('Settlements_Resolutions'[Resolution Count]), FILTER(ALL('_Date'),'_Date'[Week Rank]=MAX('_Date'[Week Rank])-1))

Proposed Measure, which returns the error "Too many arguments were passed to the FILTER function. The maximum argument count for the function is 2.":
Resolutions Week = CALCULATE(SUM('Settlements_Resolutions'[Resolution Count]), FILTER(Settlements_Resolutions, Settlements_Resolutions[Case Color Number]=3 && ALL('_Date'),'_Date'[Week Rank]=max('_Date'[Week Rank])-1))
amitchandak
Super User
1 year ago
pcook , Try like

Resolutions Week = CALCULATE(SUM('Settlements_Resolutions'[Resolution Count]), FILTER(Settlements_Resolutions, Settlements_Resolutions[Case Color Number]=3 ) && Filter(ALL('_Date'),'_Date'[Week Rank]=max('_Date'[Week Rank])-1))
pcook
Regular Visitor
1 year ago
amitchandak I used your suggestion and recieved the following error:
A function 'FILTER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

I tried many variations of your proposal, but I was not successful. As I understand the error, I am attempting to use a true/false filter for "FILTER(Settlements_Resolutions, Settlements_Resolutions[Case Color Number]=3 )" when I should be using another type of filter, but I am not sure what filter to use. Any other suggestions?

Blog Post

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