Modify DayOffset Slicer

[Anonymous]

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 

[vapid128]

OK  I don think my way gonna work for you. 

Unless you want to rewirte all measures.

[Anonymous]

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

[vapid128]

[line] is another measure,

line= countrows(dw_orders)

[Anonymous]

@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]

Try make all relationship cross filter diretion both.

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

[Anonymous]

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

[vapid128]

If you report or dateset refresh daily, 

we can try build today() function in colnum.

And numbers refresh daily.

[Anonymous]

@vapid128 yes the data is auto refreshed daily by power automate

[Anonymous]

Hi @vapid128 do you have an update please?

[vapid128]

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]

@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 · Accepted solution]

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.