Slicer Dynamic Update - Weekwise

[Anonymous]

Hi,

I have a report which I have created and published on powerbi service. This is a change management dashboard which is updated weekwise and shown to the management.

Below is the query or requirement :-

I have 2 slicers which has Start Date and End Date of these changes. 

My requirement is that whenever the week changes the slicers should dynamically change. The week pattern that we follow is Monday to Sunday. So for example if Im checking for this today or this week then the data should show from

Start Date -2nd August 00.00.00 to

End Date - 8th August 23.59.59. 

So when I check next Monday which is 9th August then Slicers should filter between

Start Date -9th August 00.00.00 to

End Date - 15th August 23.59.59

Below is the screenshot of the report for your reference.

Incase this is unclear please let me know.The Week Range that you see above is a measure so it dynamically changes. I want the Start Date and End Date work the same way so it will dynamically change and the data /charts will be updated accordingly. As of now, I change the Start Date and End Date manually and publish it to PowerBi

Note :- The Week Range that you see above in the image is a measure so it dynamically changes. I want the Start Date and End Date work the same way so it will dynamically change and the data /charts will be updated accordingly. As of now, I change the Start Date and End Date manually and publish it to PowerBi which I have to do every week.

[amitchandak · Accepted solution]

@Anonymous , You can use a relative date slicer if that can work. Else create week column

Have columns like these in date table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1

Week Number = WEEKNUM([Date],2)
Week Name= if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

Week Type = Switch( True(),
[start week]<=Today() && [end date]>=Today(),"This Week" ,
[start week]<=Today()-7 && [end date]>=Today()-7,"Last Week" ,
[Week Name]
)

Select this week and Save

Refer if needed

Default Date Today/ This Month / This Year: https://www.youtube.com/watch?v=hfn05preQYA&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=35

[amitchandak · Accepted solution]

@Anonymous , You can use a relative date slicer if that can work. Else create week column

Have columns like these in date table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1

Week Number = WEEKNUM([Date],2)
Week Name= if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

Week Type = Switch( True(),
[start week]<=Today() && [end date]>=Today(),"This Week" ,
[start week]<=Today()-7 && [end date]>=Today()-7,"Last Week" ,
[Week Name]
)

Select this week and Save

Refer if needed

Default Date Today/ This Month / This Year: https://www.youtube.com/watch?v=hfn05preQYA&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=35

[Anonymous]

Hi Amit,

Im still working on the solution you have shared since I dont have a date table so creating the same.

The youtube video link you have shared I believe will solve lot of my analytics problems im looking for to present that is Daywise Analysis, Weekwise Analysis and Monthwise Trends. Can you share the PBIX file of that date table. I believe it has everything Im looking for. Let me know if thats fine.

[Anonymous]

Thanks Amit.

I had done the relative date slicer earlier but the requirement was that it should select from Monday to Sunday every week which is as below

Start Date -9th August 00.00.00 to

End Date - 15th August 23.59.59

Not sure if we can modify the relative date this way. If it can be done it would be great because stakeholders most probably will change the filters when its published on PBI Service. Let me know if this can be done.

Il check out the 2nd solution and get back to you. Thanks in advance.

[amitchandak]

@Anonymous , For the default week to Monday, this was one solution I saw. Not sure this can help you.

https://community.powerbi.com/t5/Desktop/how-to-change-calendar-week-start-date-from-Sun-to-Mon/m-p/922487

[Anonymous]

Thanks for sharing this.

I have just skimmed through it for now and I feel it would work, but just not sure whether it will consider Time as well.

if we are taking say Monday 9th August it should pick up from date and time as well 00.00.00 because im looking at ticket counts so any miss in the time means a miss in the ticket numbers showing in the graphs. This will then reflect incorrect numbers.

also the end date would be 15th August Sunday till 23.59.59 so not sure if its taking that up. Il check it though. Thanks again..appreciate your help

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

I tested it , Monday's 0:00 and Sunday's 23.59.59 also belong to this week.

and according to the video ,I create a simple data ,as follows:

1.create a column.

Is Week = IF(WEEKNUM([Date],2)=WEEKNUM(TODAY(),2),"This week",FORMAT([Date],"mm/dd/yyyy"))

2. then the final output is shown below:

Another method:

1. enter a table

2.create a flag measure.

flag =
SWITCH (
    SELECTEDVALUE ( 'slicer'[Slicer] ),
    "This month", IF ( EOMONTH ( MAX ( [Date] ), 0 ) = EOMONTH ( TODAY (), 0 ), 1 ),
    "This week", IF ( WEEKNUM ( MAX ( [Date] ), 2 ) = WEEKNUM ( TODAY (), 2 ), 1 ),
    "This day", IF ( MAX ( [Date] ) = TODAY (), 1 )
)

3.apply it into filter.

4. the final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.