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!

Reply
Anonymous
Not applicable

Drill down - Weeks

Is it possible to drill down to weeks? I see the option for years, months, and days. Thanks! 

1 ACCEPTED SOLUTION

If you want to do the first day of the week* these are your options:

 

Query: Week = Date.StartOfWeek([Date])

 

DAX: Week = DateTable[Date] + 1 - WEEKDAY(DateTable[Date])

 

Just for fun, here's a bonus trick:

 

Problem: On a bunch of my reports I have the week as a slicer, and I always want it to default to having the current week selected. The user can move from there. If you just use the regular Week columns I've described and you select the current week, save and publish, everything will appear to be correct for the first week after you publish. But then that becomes last week, then the week before. If you're showing only current and future weeks it will end up with no default selection in the slicer which may give nonsense results on all the visuals. Or if you're still showing past weeks the default just drifts further and further into the past and becomes less useful.

 

Solution: In the query editor, add a custom column called Week.

Week = if Date.IsInCurrentWeek([Date]) 
then "This Week"
else Text.From(Date.EndOfWeek([Date]))

This results in the same thing as the simpler version of Week, but because it's in a text data type you'll get the same dates for most weeks as before, but the current week will literally say "This Week". When you use that in a slicer you can select "This Week" and it will behave exactly the same as if it read for instance "6/4/2016". But since "This Week" is a unique text value and that text value automatically moves forward on refresh to cover a new set of 7 days every Sunday, your default slicer selection will always correspond to the current calendar week so your users don't have to think about it.

 

 

* In all these examples I'm assuming a regular Sunday through Saturday week. If your business uses a different week structure like Monday through Sunday, there are ways to adapt these formulas. I leave that as an exercise to the reader.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
KHorseman
Community Champion
Community Champion

Hopefully you're using a date table already. You can add a week column to it and use that. It won't be part of the default drilldown hierarchy that you get from a single date column, but you can create your own hierarchy from a set of columns for year, month, week, and date on that table.

 

If you think of how weeks work out on calendars, they're the weirdest part of the calendar. Different types of businesses often use completely different calendar setups to work around the issue of how unevenly weeks map to months or fiscal years, so my impression is that the Power BI folks have left the handling of weeks up to the users because any out-of-the-box setup would be wrong for a huge portion of use cases.

 

I use the last date of the week as its name (i.e. this is the week of 6/4/2016). In the query editor, add a custom column called Week. The formula is = Date.EndOfWeek([Date])

 

Alternatively you can do it in the data view with DAX code instead of the query. Add a custom column

Week = DateTable[Date] + (7-WEEKDAY(DateTable[Date]))

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




If you want to do the first day of the week* these are your options:

 

Query: Week = Date.StartOfWeek([Date])

 

DAX: Week = DateTable[Date] + 1 - WEEKDAY(DateTable[Date])

 

Just for fun, here's a bonus trick:

 

Problem: On a bunch of my reports I have the week as a slicer, and I always want it to default to having the current week selected. The user can move from there. If you just use the regular Week columns I've described and you select the current week, save and publish, everything will appear to be correct for the first week after you publish. But then that becomes last week, then the week before. If you're showing only current and future weeks it will end up with no default selection in the slicer which may give nonsense results on all the visuals. Or if you're still showing past weeks the default just drifts further and further into the past and becomes less useful.

 

Solution: In the query editor, add a custom column called Week.

Week = if Date.IsInCurrentWeek([Date]) 
then "This Week"
else Text.From(Date.EndOfWeek([Date]))

This results in the same thing as the simpler version of Week, but because it's in a text data type you'll get the same dates for most weeks as before, but the current week will literally say "This Week". When you use that in a slicer you can select "This Week" and it will behave exactly the same as if it read for instance "6/4/2016". But since "This Week" is a unique text value and that text value automatically moves forward on refresh to cover a new set of 7 days every Sunday, your default slicer selection will always correspond to the current calendar week so your users don't have to think about it.

 

 

* In all these examples I'm assuming a regular Sunday through Saturday week. If your business uses a different week structure like Monday through Sunday, there are ways to adapt these formulas. I leave that as an exercise to the reader.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.