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

 

W12 Week Ending 25/03/2018

W13 Week Ending 08/04/2018

W14 Week Ending 08/04/2018

 

Any help appreciated.

 

Thanks

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.

     

     

4 Replies

  • 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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    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.

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

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