Week start date to week end date slicer

[Anonymous]

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.

[amitchandak · Accepted 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://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

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...

[v-zhenbw-msft]

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.

[v-zhenbw-msft · Accepted solution]

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.

[amitchandak · Accepted 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://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

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...

[Anonymous]

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.

[Greg_Deckler]

@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.

[pranit828]

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