Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Modify DayOffset Slicer

Hi, I'm using dayoffset column from my Date table to filter visuals in the report. For one of my tabs in the report, I'm using the day offset as a slicer but it doesn't look intuitive or user friendly compared to the default relative date slicer. Is there a way to make the day offset slicer look similar to the default relative date slicer?
Appreciate any help tamerj1 vapid128 

 

Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    I've solved the issue by adding a blank query called "Now" that specifies the timezone and then referencing that "Now" query in the Date table i.e. the "Current date" step in power query.

12 Replies

  • vapid128's avatar
    vapid128
    Solution Specialist
    4 years ago

     

    Is this what you want? 

     

    Create 4 tables

    First:

    type

    day
    week(Mon-Sun)
    Month

     

    Second:
    Next/Last

    Next
    Last

     

    Third:

    VALUE = GENERATESERIES(0,100)
    Fourth:
    dateTable = CALENDAR(MIN(dw_orders[date]),MAX(dw_orders[date]))
     
    In dateTable create colnums:
    MonthIndex = YEAR([Date])*12+MONTH([Date])
    WeekIndex(MtoS) = int(([Date]-2)/7)
     
    Create Measure:
    Measure =
    var _NextLast = IF(SELECTEDVALUE('Next/Last'[Next/Last])="Last",-1,1)

    return
    SWITCH(TRUE(),
        SELECTEDVALUE('type'[type])="Month" ,
        CALCULATE(
            [lines],
            FILTER(
                'dateTable',
                dateTable[MonthIndex]= year(TODAY())*12 + MONTH(TODAY())+SELECTEDVALUE('VALUE'[Value])*_NextLast
            )
        ),
        SELECTEDVALUE('type'[type])="week(Mon-Sun)" ,
        CALCULATE(
            [lines],
            FILTER(
                'dateTable'
                ,dateTable[WeekIndex(MtoS)]=  int((TODAY()-2)/7)+SELECTEDVALUE('VALUE'[Value])*_NextLast
            )
        ),
        CALCULATE(
            [lines],
            FILTER(
                'dateTable',
                dateTable[Date]=  TODAY()+SELECTEDVALUE('VALUE'[Value])*_NextLast
            )
        )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      vapid128 thanks for this but I have one question - in your 'Measure' function, what does [lines] represent??

      Date Offset Slicer =
var _NextLast = IF(SELECTEDVALUE('Date Slicer-Next/Last'[Next/Last]) ="Last",-1,1)

return
SWITCH(TRUE(),
    SELECTEDVALUE('Date Slicer-Type'[Type])="Month" ,
    CALCULATE(
        [lines],
        FILTER(
            'Calendar Offset',
            'Calendar Offset'[Month Index]= year(TODAY())*12 + MONTH(TODAY())+SELECTEDVALUE('VALUE'[Value])*_NextLast
        )
    ),
    SELECTEDVALUE('Date Slicer-Type'[Type])="Week" ,
    CALCULATE(
        [lines],
        FILTER(
            'Calendar Offset'
            ,'Calendar Offset'[Week Index]=  int((TODAY()-2)/7)+SELECTEDVALUE('VALUE'[Value])*_NextLast
        )
    ),
    CALCULATE(
        [lines],
        FILTER(
            'Calendar Offset',
            'Calendar Offset'[Date]=  TODAY()+SELECTEDVALUE('VALUE'[Value])*_NextLast
        )
    )
)

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        I've solved the issue by adding a blank query called "Now" that specifies the timezone and then referencing that "Now" query in the Date table i.e. the "Current date" step in power query.

  • vapid128's avatar
    vapid128
    Solution Specialist
    4 years ago

    [line] is another measure,

    line= countrows(dw_orders)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      vapid128 , you have date table linked to one table for your example so countrows will be for 'dw_orders' table only. I will be linking to multiple tables - how do I accomplish that?

      • vapid128's avatar
        vapid128
        Solution Specialist
        4 years ago

        Try make all relationship cross filter diretion both.

         

        If it does not work, can you pos all your table relationships?

  • vapid128's avatar
    vapid128
    Solution Specialist
    4 years ago

    OK  I don think my way gonna work for you. 

    Unless you want to rewirte all measures.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      vapid128 I appreciate your help and feedback but is there another way without rewriting all measures? If not possible, then it's okay I'll seek further advise from other members in the forum.