Get Next Larger DateTime

[UncleLewis]

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

[UncleLewis]

Thanks Ashish,

Works perfectly!

Thanks,
-w

[Ashish_Mathur]

You are welcome.

[parry2k]

@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

[UncleLewis]

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

[Ashish_Mathur · 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.