Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Apsawhney
Helper I
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 - 

 

PartnerLatest Registration
Apple10/31/19 10:01 AM
Apple10/31/19 9:47 AM
Apple10/31/19 9:07 AM
Apple10/31/19 9:01 AM
Apple10/31/19 1:50 AM
Apple10/31/19 1:44 AM
Apple10/31/19 1:44 AM
Apple10/31/19 1:39 AM
Apple10/31/19 1:08 AM
Apple10/31/19 1:06 AM
Fitbit10/31/19 10:00 AM
Fitbit10/31/19 9:43 AM
Fitbit10/31/19 9:33 AM
Fitbit10/31/19 9:32 AM
Tranggle10/30/19 5:47 AM

 

Thresholds - 

 

PartnerThresholds
AppleRegistration in the Last 2 Hours
FitbitRegistration in the Last 6 Hours
TranggleRegistration 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
Apsawhney
Helper I
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. 

View solution in original post

5 REPLIES 5
Apsawhney
Helper I
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. 

v-lid-msft
Community Support
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 )
        <
        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.

 

2.PNG



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

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

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors