cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Compare timestamps in Dax

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.

1 ACCEPTED SOLUTION
Helper I

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.

5 REPLIES 5
Helper I

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.

Community Support

Hi @Apsawhney ,

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

``````ISINThresholds =
IF (
DATEDIFF ( MAX ( 'Table'[Latest Registration] ), [CurrentTime], HOUR )
<
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.

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.
Resolver II

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

Resolver II

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

Resolver II

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors