Measure to Compare varying Date and Time with a fixed time variable date.

[Spudduk]

Hi,

After a bit of help if possible.

I have a field which looks a bit like this in a very simple way.

Category ||  Date + Time . 

Item 1          11/11/2022 18:00:76

Item 2          11/11/2022  21:08:67

Comparison Time to compare to.

Item 1           19:00:00

Item 2           21:00:00

The logic I am looking is to add the current date to the fixed time and compare the items to see if before or after.

So in the example above Item 1 would be Before and Item 2 would be After.

The issue is that the data comes from a model so I cannot add any tables or colums to the data so have to do it in a measure instead.

TIA

Andy

[MahyarTF · Accepted solution]

Hi @Spudduk ,

Please use below code for create measure :

__Condition =

Var __CurrDate = CALCULATE( Max( Sheet29[Date Time] ) )

Var __Currtime = Time( Hour(__CurrDate), MINUTE(__CurrDate), SECOND(__CurrDate))

Return if( time(Hour(__Currtime),

                MINUTE(__Currtime),

                SECOND(__Currtime)) < Time( Hour(CALCULATE( Max( Sheet30[Time]))),

                                            MINUTE(CALCULATE( Max( Sheet30[Time]))),

                                            SECOND(CALCULATE( Max( Sheet30[Time]))))

           , "Before"

           , "Equal or After"

        )

Appreciate your Kudos and Please mark it as a solution if it helps you.