cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kelly4dm Regular Visitor
Regular Visitor

Drill down - Weeks

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
KHorseman Super Contributor
Super Contributor

Re: Drill down - Weeks

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? Please mark it as a solution.

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
KHorseman Super Contributor
Super Contributor

Re: Drill down - Weeks

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? Please mark it as a solution.

Proud to be a Datanaut!
Highlighted
KHorseman Super Contributor
Super Contributor

Re: Drill down - Weeks

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? Please mark it as a solution.

Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 56 members 714 guests
Please welcome our newest community members: