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 friendl...
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.

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.