topic Modify DayOffset Slicer in DAX Commands and Tips

Modify DayOffset Slicer

Anonymous — Sun, 24 Jul 2022 06:46:50 GMT

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

Re: Modify DayOffset Slicer

vapid128 — Mon, 25 Jul 2022 17:56:00 GMT

Is this what you want?

Create 4 tables

First:

type

day

week(Mon-Sun)

Month

Second:
Next/Last

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

)

Re: Modify DayOffset Slicer

Anonymous — Mon, 25 Jul 2022 20:33:04 GMT

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 ) ) )

Re: Modify DayOffset Slicer

vapid128 — Mon, 25 Jul 2022 20:37:43 GMT

[line] is another measure,

line= countrows(dw_orders)

Re: Modify DayOffset Slicer

Anonymous — Mon, 25 Jul 2022 20:58:27 GMT

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?

Re: Modify DayOffset Slicer

vapid128 — Mon, 25 Jul 2022 21:37:14 GMT

Try make all relationship cross filter diretion both.

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

Re: Modify DayOffset Slicer

Anonymous — Mon, 25 Jul 2022 21:52:30 GMT

vapid128 I'm not comfortable setting cross filter to 'both' direction as it may cause ambiguity with results when filtering. I direct messaged you with link to pbix file. Hope that's okay.

Re: Modify DayOffset Slicer

vapid128 — Mon, 25 Jul 2022 23:25:17 GMT

OK I don think my way gonna work for you.

Unless you want to rewirte all measures.

Re: Modify DayOffset Slicer

Anonymous — Mon, 25 Jul 2022 23:30:00 GMT

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.

Re: Modify DayOffset Slicer

vapid128 — Tue, 26 Jul 2022 17:27:43 GMT

If you report or dateset refresh daily,

we can try build today() function in colnum.

And numbers refresh daily.

Re: Modify DayOffset Slicer

Anonymous — Tue, 26 Jul 2022 19:12:54 GMT

vapid128 yes the data is auto refreshed daily by power automate

Re: Modify DayOffset Slicer

Anonymous — Wed, 27 Jul 2022 21:47:07 GMT

Hi vapid128 do you have an update please?

Re: Modify DayOffset Slicer

Anonymous — Sat, 30 Jul 2022 08:47:00 GMT

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.