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

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.