Create a Week number slicer with a week ending description

[JohnA]

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

[Greg_Deckler]

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

[Thimios]

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 · Accepted solution]

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.

Regards,

Suguna.

[Anonymous]

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