cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
UncleLewis
Super User
Super User

Get Next Larger DateTime

Hi all,

 

How to get next larger datetime from another table?
I have tblStart and tblEnd
I am trying to build a new table so I cna use the datetime fields to calculate elapsed time.

 

Problem is, I am getting a a few where the end time is before the start time

This is not possible

 

I investigated some log files and found it is really not a match, the End times are just not coded well enough to give a good match so the query is a bit more open.

 

This openness is returning some end times mixed in to the end times I am really not interested in

The next best option is to use an approximate match to get the closest but larger end time.

 

How to do that with DAX?

 

Thanks

-w

1 ACCEPTED SOLUTION

Hi,

Write this calculated column formula in the tblHold Table

=calculate(min(tblRelease[date_time]),filter(tblRelease,tblRelease[work_order_id]=earlier(tblHold[work_order_id])&&tblRelease[date_time]>earlier(tblHold[date_time])))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
UncleLewis
Super User
Super User

Thanks Ashish,

 

Works perfectly!

Thanks,
-w

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
parry2k
Super User
Super User

@UncleLewis Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Thanks parry2k,

Here is a screen shot of my source tables and the final table I am trying to achieve
I have a table with hold times
I have another table with release times
The tables have the same work_order_id
However for the release time there may be 0-n records for release time
Additionally, there are release times that are before the hold time. These should be skipped as the relase time must always follow the hold time.

I think an approximate match should work, just not sure how to do that in DAX.

tbl_h_r.png
Thanks,
-w

 

Hi,

Write this calculated column formula in the tblHold Table

=calculate(min(tblRelease[date_time]),filter(tblRelease,tblRelease[work_order_id]=earlier(tblHold[work_order_id])&&tblRelease[date_time]>earlier(tblHold[date_time])))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power BI Dev Camp Session 27

Ted's Dev Camp

This session walks through creating a new Azure AD B2C tenant and configuring it with user flows and custom policies.