Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm trying to create a week slicer that will filter my entire report. I already created a week start date and week end date column and I need to know how to create a week range filter measure/column.
For example, i'd like my entire report to filter its data to week 8/9/2020- 8/15/2020 when selected in the slicer based on the amount of weeks in the date field in my table.
Solved! Go to Solution.
@oriabb, you can contact both
Week : [start date of week] & " - " & [weekend date]
O
Week - format([start date of week],"mm/dd/yyyy") & " - " & format([week end date],"mm/dd/yyyy")
submit my blogs in the week
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
Hi @Anonymous ,
We can create a date table and a measure to meet your requirement, pay attention there is no relationships between tables.
1. Create a date table, add a weeknum column and create a slicer column.
Date = CALENDAR("2020/8/1","2020/10/1")
weeknum = WEEKNUM('Date'[Date],2)
Slicer =
var _start_date = CALCULATE(MIN('Date'[Date]),FILTER('Date','Date'[weeknum]=EARLIER('Date'[weeknum])))
var _end_date = CALCULATE(MAX('Date'[Date]),FILTER('Date','Date'[weeknum]=EARLIER('Date'[weeknum])))
return
_start_date &" - "& _end_date
2. Then create a measure.
Measure =
var _select = SELECTEDVALUE('Date'[Slicer])
var _start = CALCULATE(MIN('Date'[Date]),FILTER('Date','Date'[Slicer]=_select))
var _end = CALCULATE(MAX('Date'[Date]),FILTER('Date','Date'[Slicer]=_select))
return
CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date]>=_start && 'Table'[Date]<_end))
3. Put it in a table visual with Date column in Table.
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi @Anonymous ,
How about the result after you follow the suggestions mentioned in my original post?
Could you please provide more details or expected result about it If it doesn't meet your requirement?
If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
We can create a date table and a measure to meet your requirement, pay attention there is no relationships between tables.
1. Create a date table, add a weeknum column and create a slicer column.
Date = CALENDAR("2020/8/1","2020/10/1")
weeknum = WEEKNUM('Date'[Date],2)
Slicer =
var _start_date = CALCULATE(MIN('Date'[Date]),FILTER('Date','Date'[weeknum]=EARLIER('Date'[weeknum])))
var _end_date = CALCULATE(MAX('Date'[Date]),FILTER('Date','Date'[weeknum]=EARLIER('Date'[weeknum])))
return
_start_date &" - "& _end_date
2. Then create a measure.
Measure =
var _select = SELECTEDVALUE('Date'[Slicer])
var _start = CALCULATE(MIN('Date'[Date]),FILTER('Date','Date'[Slicer]=_select))
var _end = CALCULATE(MAX('Date'[Date]),FILTER('Date','Date'[Slicer]=_select))
return
CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Date]>=_start && 'Table'[Date]<_end))
3. Put it in a table visual with Date column in Table.
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
@oriabb, you can contact both
Week : [start date of week] & " - " & [weekend date]
O
Week - format([start date of week],"mm/dd/yyyy") & " - " & format([week end date],"mm/dd/yyyy")
submit my blogs in the week
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
Hi @amitchandak . This dax worked perfectly. Week : [start date of week] & " - " & [weekend date].
I've run into another issue though. Within the week slicer, i'd like to set the filter to show previous week's data. The other weeks will still show when the user selects the filter but i want the previous week set as default. Can you please help or point me to the right direction for the solution? Btw, i dont have a relative option in the dropdown since the week start date and the week end date are columns i created in my table based on the date field.
@Anonymous - I'm not entirely clear on what you are trying to do. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
HI @Anonymous
Please use WEEKNUM function
https://docs.microsoft.com/en-us/dax/weeknum-function-dax
week 8/9/2020- 8/15/2020 will be displayed as weeknum 33
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |