Forum Discussion

asheyashey's avatar
asheyashey
Helper I
10 months ago
Solved

Make a slicer for Current week,Week+1,Week+2,Week+3

As mentioned in Title.

I want a slicer like this.
I have a date column(dd/mm/yyyy). Can anyone guide me how to approach this specific problem.

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Bmejia's avatar
    Bmejia
    10 months ago

    Seems like yo uneed to filter on 5 weeks so you can create column preferably on your calendar/date table, but can be also be use on any date column, but you could be missing weeks where there is no value for the week. 

    Create a column:  0 will be your current week, 1 next week etc. or -1 previous week  -2 previous week

    Relative Week = DATEDIFF(TODAY(),'Date'[Date], WEEK)

    You can also create another column to play around with the naming convention. 
    example:
    WeekDesc =
    SWITCH(TRUE(),
    DATEDIFF(TODAY(),'Date'[Date], WEEK) = 0, "Current Week"  -- Week 1
    ,DATEDIFF(TODAY(),'Date'[Date], WEEK) = 1, "Week 2"
    ,DATEDIFF(TODAY(),'Date'[Date], WEEK) = 2, "Week 3"
    ,DATEDIFF(TODAY(),'Date'[Date], WEEK) = 3, "Week 4",
    "Week +" & 'Date'[Relative Week])

    when you create your slicer just filter on the weeks you want to bring. in the example would be relative week 0, 1, 2, 3, 4.  Next week then it would move all the numbers up or down depending if you are looking at next or previous week.

     




5 Replies

  • Bmejia's avatar
    Bmejia
    Super User
    10 months ago

    Seems like yo uneed to filter on 5 weeks so you can create column preferably on your calendar/date table, but can be also be use on any date column, but you could be missing weeks where there is no value for the week. 

    Create a column:  0 will be your current week, 1 next week etc. or -1 previous week  -2 previous week

    Relative Week = DATEDIFF(TODAY(),'Date'[Date], WEEK)

    You can also create another column to play around with the naming convention. 
    example:
    WeekDesc =
    SWITCH(TRUE(),
    DATEDIFF(TODAY(),'Date'[Date], WEEK) = 0, "Current Week"  -- Week 1
    ,DATEDIFF(TODAY(),'Date'[Date], WEEK) = 1, "Week 2"
    ,DATEDIFF(TODAY(),'Date'[Date], WEEK) = 2, "Week 3"
    ,DATEDIFF(TODAY(),'Date'[Date], WEEK) = 3, "Week 4",
    "Week +" & 'Date'[Relative Week])

    when you create your slicer just filter on the weeks you want to bring. in the example would be relative week 0, 1, 2, 3, 4.  Next week then it would move all the numbers up or down depending if you are looking at next or previous week.

     




  • GeraldGEmerick's avatar
    GeraldGEmerick
    Super User
    10 months ago

    asheyashey There are two methods of approaching this problem. One, you could create a separate table with those slicer values and then use a measure to form the relationship between the slicer values and your data. For example:

    Measure = 
VAR _Today = TODAY()
VAR _Year = YEAR( _Today )
VAR _WeekNum = WEEKNUM( _Today )
VAR _Date = MAX( 'Table'[Date] )
VAR _YearDate = YEAR( _Date )
VAR _WeekNumDate = WEEKNUM( _Date )
VAR _Return = IF( _Year = _YearDate && _WeekNum = _WeekNumDate, 1, 0 )
RETURN _Return

    That's an example. The second method would be to create a date table that has offsets or sequential numbers or does a similar type of calculation in a column where you essentially calculate the week difference between TODAY and the date in the table.

  • mh2587's avatar
    mh2587
    Super User
    10 months ago

    Why you're not using relative date slicer for that, like below or create a week column

     

    • v-menakakota's avatar
      v-menakakota
      Community Support
      10 months ago

      Hi asheyashey ,
      Thanks for reaching out to the Microsoft fabric community forum. 

       

      I would also take a moment to thank Bmejia  and mh2587  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

      I hope the below details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you 

       

       

      Best Regards, 
      Community Support Team  

      • v-menakakota's avatar
        v-menakakota
        Community Support
        10 months ago

        Hi  asheyashey  ,

        I hope the below details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

         

        Best Regards, 
        Community Support Team.