6am to 6am filter in Slicer, but with single timestamp table

[TejasShah]

Hello all. I have this table where I have my timestamp(bins) where I need the data from only 6am of FromDate to 6am of To Date.

I have tried the following solution from a couple of post, but I am unable to understand what should be defined to variable 

vCurrentLogDatetime

6To6Flag =

VAR vStartDate =

    CALCULATE ( MIN ( Meter_Consumption_Table[timestamp (bins)] ), ALLSELECTED( ( Meter_Consumption_Table[timestamp (bins)] ) ))

VAR vEndDate =

    CALCULATE ( MAX ( Meter_Consumption_Table[timestamp (bins)] ), ALLSELECTED ( Meter_Consumption_Table[timestamp (bins)] ) )

VAR vCurrentLogDatetime =

    MAX ( Meter_Consumption_Table[timestamp (bins)] )

VAR vCurrentLogDate =

    INT ( vCurrentLogDatetime )

VAR vCurrentLogTime =

    TIME ( HOUR ( vCurrentLogDatetime ), MINUTE ( vCurrentLogDatetime ), SECOND ( vCurrentLogDatetime ) )

VAR vResult =

    SWITCH (

        TRUE,

        vCurrentLogDate = vStartDate, IF ( vCurrentLogTime > TIME ( 6, 0, 0 ), 1, 0 ),

        vCurrentLogDate = vEndDate, IF ( vCurrentLogTime < TIME ( 6, 0, 0 ), 1, 0 ),

        vCurrentLogDate > vStartDate && vCurrentLogDate < vEndDate, 1,

        0

    )

RETURN

    vResult

Using this I am able to get filter for FromDate but unable to get the same for ToDate:

Can anyone please support, this measure was suggested by @DataInsights in that particular post.

I am doubtful about the vCurrentLogDatetime variable.

[Chocohammer · Accepted solution]

Hi @TejasShah 

I'm not an expert, but I had a similar problem with Slicer for the 12 hour shift. Maybe you can use my solution to the problem and custom it.

I would solve this not in the measure but rather add a new column to the table. If you have a table with a start and end in one row, you can easily use this IF statement:

6to6Flag =

IF(

 HOUR ( Data[start] )  >= 6 /*you can use = for both if it's only one day timestamp like a plan between*/

        && HOUR ( Data[end] ) <= 18, "True",
"False"
)

If the start and end data are not in the same row, try using SWITCH / IF:

6to6Flag =

SWITCH(

    TRUE(),

    HOUR(Data[timestamp]) >= 6 && HOUR(Data[timestamp]) < 18, "True",

    "False"

)

Then, if there is no problem with relationships for the filtering, you can hard filter the rest of the data out with the True/False statement. Hope this will help or maybe someone will have a better idea 🙂

Have a good one

Choco

[TejasShah]

Hello @Chocohammer , soory for my late response.

But what I am finding is filter from 6am of From date to the 6am of To date, so this filter of 6 and 18 hours won't be feasible in my case.

Can you please suggest any other way out?