Forum Discussion
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...
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 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
)
)
)
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.