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:

Pre-requisites:

https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441

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?

Yes. Refer : https://community.powerbi.com/t5/Community-Blog/Week-That-Resets-Yearly-Decoding-Date-and-Calendar-3-5-Power-BI/ba-p/1187502

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.

About the Author: Amit Chandak

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.

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