Forum Discussion
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.
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]))
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.