cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JohnA
Frequent Visitor

Create a Week number slicer with a week ending description

Hi,

 

I'm trying to create a slicer using week number but I need the week ending as the description as the week number isn't obvious to the end user.  For example, I would like the user to see

 

W12 Week Ending 25/03/2018

W13 Week Ending 08/04/2018

W14 Week Ending 08/04/2018

 

Any help appreciated.

 

Thanks

1 ACCEPTED SOLUTION
bidevsugmen
Resolver I
Resolver I

Hi,

 

You can use date transformations available (retrieving week number and end of week from the date column) in date table.
After this, concatenate the values in required format and create a custom column, which can be used in the slicer. Hope this helps out.Screenshot_46.png

Regards,

Suguna.

 

 

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

Given a date table like:

 

Calender = CALENDAR(DATE(2017,1,1),DATE(2018,12,31))

Create this column:

 

WeekEnding = 
VAR myWeekNum = WEEKNUM([Date])
VAR myYear = YEAR([Date])
VAR myEndDate = CALCULATE(MAX([Date]),FILTER(ALL(Calender),YEAR([Date])=myYear && Calender[WeekNum]=myWeekNum && Calender[WeekDay]=7))
VAR myEndDate1 = IF(NOT(ISBLANK(myEndDate)),myEndDate,CALCULATE(MAX([Date]),FILTER(ALL(Calender),YEAR([Date])=myYear+1 && Calender[WeekNum]=1&&Calender[WeekDay]=7)))
VAR myEndDate2 = IF(NOT(ISBLANK(myEndDate1)),myEndDate1,MAX([Date]))
RETURN "W" & myWeekNum & " Week Ending " & myEndDate2

 

 Here it is as a measure if you should ever need such a thing:

 

mWeekEnding = 
VAR myDate = MAX(Calender[Date])
VAR myWeekNum = WEEKNUM(myDate)
VAR myYear = YEAR(myDate)
VAR maxDate = DATE(2017,1,1)
VAR minDate = DATE(2018,12,31)
VAR dateTable = CALENDAR(maxDate,minDate)
VAR dateTable1 = ADDCOLUMNS(dateTable,"WeekNum",WEEKNUM([Date]))
VAR dateTable2 = ADDCOLUMNS(dateTable1,"WeekDay",WEEKDAY([Date]))
VAR weekEndDate = CALCULATE(MAXX(FILTER(dateTable2,YEAR([Date])=myYear&&[WeekNum]=myWeekNum&&[WeekDay]=7),[Date]))
VAR weekEndDate1 = IF(NOT(ISBLANK(weekEndDate)),weekEndDate,CALCULATE(MAXX(FILTER(dateTable2,YEAR([Date])=myYear+1&&[WeekNum]=1&&[WeekDay]=7),[Date])))
VAR weekEndDate2 = IF(NOT(ISBLANK(weekEndDate1)),weekEndDate1,MAXX(dateTable2,[Date]))
RETURN "W" & myWeekNum & " Week Ending " & weekEndDate2

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

Hi Greg,

Tried to apply that but i get an error (ie W27 Week Ending FALSE). I suppose there is something missing in [myEndDate1] if not true.

Do you mind taking a look?

WeekNumEnding =
VAR myWeekNum = WEEKNUM([Date])
VAR myDate = MAX(Calendar[Date])
VAR myYear = YEAR([Date])
VAR myEndDate = CALCULATE(MAX([Date]);FILTER(ALL('Calendar');YEAR([Date])=myYear && value('Calendar'[WeekNum])=myWeekNum && VALUE('Calendar'[WeekdayNum])=7))
VAR myEndDate1 = IF(NOT(ISBLANK(myEndDate));myEndDate = CALCULATE(MAX([Date]);FILTER(ALL('Calendar');YEAR([Date])=myYear+1 && value('Calendar'[WeekNum])=myWeekNum && VALUE('Calendar'[WeekdayNum])=7)))
VAR myEndDate2 = IF(NOT(ISBLANK(myEndDate1));myEndDate1;MAX([Date]))
RETURN "W"& myWeekNum & " Week Ending " & myEndDate2

 

bidevsugmen
Resolver I
Resolver I

Hi,

 

You can use date transformations available (retrieving week number and end of week from the date column) in date table.
After this, concatenate the values in required format and create a custom column, which can be used in the slicer. Hope this helps out.Screenshot_46.png

Regards,

Suguna.

 

 

Anonymous
Not applicable

Create this field in your date table.  When you set up your slicer, do so using this new field in your Date table.

 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors