Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |