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.
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
Make sure your calendar has the following fields.(For a financial year, which is not same as the calendar year, refer the Power BI community blog for some of these calculations.)
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])- What are various functions for Week Till Date?
Power BI does not provide any function for week intelligence.
- How would we go about week Intelligence like This Week, Last Week,and WTD etc?
Rank will help in that, one of the most powerful function in power BI. You can refer to my blog, Week is not so Weak.
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])))
- Our week does not start on a Sunday or Monday. How do we deal with this in Power BI?
You can create a week where starting date of the week can be of any weekday using this blog.
- How can we get same weekday last year(For example, last Tuesday almost a year back)?
Same weekday last year is 364 days behind.
Same Week Day Last year =CALCULATE([Sales], dateadd('Date'[Date],-364,Day))Similarly, same weekday last week is 7 days behind.
Same Week Day Last Week = CALCULATE([Sales], dateadd(‘Date’[Date],-7,Day))- How can we get rolling weeks, like last 4 weeks, 6 weeks etc? It means that, this week data is a sum or average of last N weeks?
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])))
- How can we get last year same week?
Two ways, either 52 weeks back or same week number last year.
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])) ))
- What about last year WTD ?
Again two ways,either 52 weeks back or same week number last year.
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])))
- We have only week number wise data (There is no date in the data). How can we get This Week vs Last Week comparison or Rolling N weeks data?
As long as you can create an incremental rank you should be able to do that, in the same manner, we have explained in the question: How would we go about week Intelligence like This Week, Last Week, and WTD etc?
- We have a Week Year, can it be converted to a week start date?
Yes , depending on the format. Let us take the [week] shared in pre-requisite section above and try to convert it back to a date (Week Start Date).
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- Can we reset the weeks to start from year start date, every year?
Let us know what you think about the above suggested methods.
Share the scenario where it helped you. Please find the file, for you to explore.
Also, would love to hear your approach on the alternate methods of using above Time Intelligence functions.
Want to Learn More, Explore these videos and video series.
Complete Power BI Tutorial/Course in Just 11 Hours
Mastering Microsoft Fabric: 30 Videos
Want to Learn SQL: 20 Videos
Mastering Power BI: 230+ Videos
Power BI Concepts- How Power BI Works- 150+ Videos
Migrating from Tableau to Power BI: 30 Videos
DAX for SQL Users: 10 Videos
What works in Power BI Direct Query- 10+ Videos
सीखे Power BI हिंदी में- 60+ Videos
Power BI Hindi On Demand- 10+ Videos
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.
Power BI is turning 5! In celebration of this achievement, Amit is sharing 5 blog posts on the community, Series: Power BI turns 5. You can find the links in this post.
If there is any other topic in your mind, do not hesitate to reach out to Amit. He is looking for different talking points to share his thoughts on Power BI.
You can follow his work on LinkedIn, Twitter, Medium, Youtube.
