Forum Discussion
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
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.
5 Replies
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/38490Thanks 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,
-wHi,
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,
-wYou are welcome.
