cancel
Showing results for
Did you mean:
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
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.

Regards,

Suguna.

4 REPLIES 4
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 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```

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition
Helper I

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

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.

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.

Announcements