Compare timestamps in Dax

[Apsawhney]

Hi,

I have a table which has the hourly registrations per partner. I need a measure which gives true/false if the latest registration for a partner is within the threshold. I have different thresholds for different partners. 

Sample table - 

Partner	Latest Registration
Apple	10/31/19 10:01 AM
Apple	10/31/19 9:47 AM
Apple	10/31/19 9:07 AM
Apple	10/31/19 9:01 AM
Apple	10/31/19 1:50 AM
Apple	10/31/19 1:44 AM
Apple	10/31/19 1:44 AM
Apple	10/31/19 1:39 AM
Apple	10/31/19 1:08 AM
Apple	10/31/19 1:06 AM
Fitbit	10/31/19 10:00 AM
Fitbit	10/31/19 9:43 AM
Fitbit	10/31/19 9:33 AM
Fitbit	10/31/19 9:32 AM
Tranggle	10/30/19 5:47 AM

Thresholds - 

Partner	Thresholds
Apple	Registration in the Last 2 Hours
Fitbit	Registration in the Last 6 Hours
Tranggle	Registration in the Last 4 Hours

So taking our current time as "10/31/2019 11:00 AM" then based on above raw data, the latest "Apple" registration which is 10/31/19 10:01 AM is within its threshold of being in the past 2 hours i.e. Current time-Latest Registration < 2 hrs. So here teh measure will return true.

Based on this the registration for Tranggle is not in threshold so the measure should return a value as False. 

Can anyone help here. I tried creating a measure with IF clauses but it doesn't seem to compare the data correctly. 

[Apsawhney · Accepted solution]

Hi All,

I tried all the solutions you have provided but wasn't quite able to achieve my objective. 

What I have done now is created a new table and using M queries I am storing the dynamic thresholds for each partner. 

For e.g. 

if (List.Contains({"Apple","Fitbit","Samsung"},[Partner])) then Value.Subtract(DateTime.LocalNow(),#duration(0,3,0,0)) else if (List.Contains({"Garmin","Polar"},[Partner])) then Value.Subtract(DateTime.LocalNow(),#duration(0,4,0,0)).....

Now I am able to compare these dynamic values using the dateDiff function. 

[v-lid-msft]

Hi @Apsawhney ,

We can create a measure using following formula to meet your requirement:

ISINThresholds =
IF (
    DATEDIFF ( MAX ( 'Table'[Latest Registration] ), [CurrentTime], HOUR )
        <
        VAR Thread =
            LOOKUPVALUE (
                'Thresholds'[Thresholds],
                'Thresholds'[Partner], SELECTEDVALUE ( 'Table'[Partner] )
            )
        RETURN
            VALUE (
                SUBSTITUTE (
                    SUBSTITUTE ( Thread, "Registration in the Last ", "" ),
                    " Hours",
                    ""
                )
            ),
    TRUE (),
    FALSE ()
)

You can relpace the [CurrentValue] as the NOW() Function.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bpsearle]

Hey,

This wouldn’t work as a measure. It would be a calculated column on the table.

Use DATEDIFF() and NOW(). Specify MINUTES for the comparison. You could either add IF logic to the same calculated column to decode the difference into the bands you want or have a separate calculated column to do that. Depends how you like to structure your code for testing etc.

When do you want NOW() to be updated?

Thanks, Brian

[bpsearle]

Apologies I missed the bit about latest registration, let me have another look

[bpsearle]

Hey

Do a google for “dax max date in group by”. I’m thinking you will need 2 expressions, the first to find the max time for the partner, the second to compare each row time against the max time. This will be an iterator function e.g. sumx.

I need more time to convert this to DAX and don’t have any left today. If you haven’t managed to solve this by tomorrow, I should have time to look then.

Thanks, Brian