Forum Discussion

JohnA's avatar
JohnA
Frequent Visitor
8 years ago
Solved

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   ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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.

     

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
8 years ago

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's avatar
Thimios
Icon for Helper III rankHelper III
6 years ago

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