Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
Solved! Go to 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.
Thanks Ashish,
Works perfectly!
Thanks,
-w
You are welcome.
@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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
63 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
64 | |
59 | |
56 |