Power BI — WTD Questions— Time Intelligence 4–5

In this blog, we will try to answer, 10 Most Frequently Asked Questions on Week Till Date(WTD) in Power BI.

Abbreviations:

WTD : Week Till Date
LWTD : Last Week Till Date

Pre-requisites:

For any Time Intelligence, you must have a date table. Please refer the below links to create one date table.

Date = CALENDAR(date(2015,03,01),Date(2021,02,28)) //calendar
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")
Year = YEAR('Date'[Date])
This Week = CALCULATE([Sales], FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))Last Week = CALCULATE([Sales], FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))WTD = CALCULATE([Sales], FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[Weekday] <=max('Date'[Weekday])))LWTD = CALCULATE([Sales], FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -1) && 'Date'[Weekday] <=max('Date'[Weekday])))
Same Week Day Last year =CALCULATE([Sales], dateadd('Date'[Date],-364,Day))
Same Week Day Last Week = CALCULATE([Sales], dateadd(‘Date’[Date],-7,Day))
Last 4 week Sales = CALCULATE([Sales],FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))Last 6 week Sales = CALCULATE([Sales],FILTER(all('Date'),'Date'[Week Rank]>=min('Date'[Week Rank])-6 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
Last year same Week = CALCULATE([Sales], FILTER(ALL(‘Date’),’Date’[Week Rank]=(max(‘Date’[Week Rank]) -52)))Last year Week = CALCULATE([Sales], FILTER(ALL(‘Date’),’Date’[Year]=(max(‘Date’[Year]) -1) && ‘Date’[Week Number]=(max(‘Date’[Week Number])) ))
LYWTD = CALCULATE([Sales], FILTER(ALL(‘Date’),’Date’[Week Rank]=(max(‘Date’[Week Rank]) -52) && ‘Date’[Weekday] <=max(‘Date’[Weekday]))) LYWTD Year = CALCULATE([Sales], FILTER(ALL(‘Date’),’Date’[Year]=(max(‘Date’[Year]) -1) && ‘Date’[Week Number]=(max(‘Date’[Week Number])) && ‘Date’[Weekday] <=max(‘Date’[Weekday])))
Week to Date = var _st = date(left([Week],4),1,1)
var _week = right([Week],2)*1
Return _st+((_week-1)*7) -WEEKDAY(_st,2)+1

About the Author: Amit Chandak

Amit Chandak is Chief Analytics Officer at Kanerika Software Private Limited where the priority is to get the most value out of the customers’ data. He is also an avid Power BI Super User due to his contributions to the Power BI community.

Amit has 18+ years of experience in Business Intelligence, Analytics, Data Science. He is Chief Analytics Officer at Kanerika & Power BI community Super User

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store