Output rows based on matching date time intervals

[Anonymous]

Hi, 

I need help on the same requirement which is handled in the below post, but when there is a many to many relationship. 

Solved: DAX: Flag order if timestamp is in between 2 dates... - Microsoft Power BI Community

Table B will have multiple deal intervals on the same product differentiated using a timestamp.

Modified Table B

deal_id	start_date_time	end_date_time	product_id
1	02.01.2021 10:00:000	04.01.2021 14:00:00	987654321
2	05.01.2021 8:00:00	05.01.2021 8:25:00	123456789
3	05.01.2021 10:00:00	05.01.2021 10:25:00	123456789

Modified Table A - order positions (multiple orders on same product at different times)

order_id	timestamp	product_id
123	05.01.2021 8:10:00	123456789
456	04.01.2021 16:00:00	987654321
789	05.01.2021 10:15:00	123456789
321	08.01.2021 8:20:15	987654321
654	12.01.2021 6:15:15	345876112
987	15.01.2021 6:10:15	345876112
321	05.01.2021 8:15:00	123456789

Output:

deal_id	start_date_time	end_date_time	product_id	Order_id
2	05.01.2021 8:00:00	05.01.2021 8:25:00	123456789	123
2	05.01.2021 8:00:00	05.01.2021 8:25:00	123456789	321
3	05.01.2021 10:00:00	05.01.2021 10:25:00	123456789	789

Any lead on this would be of great help!

Thanks.

[lbendlin]

Do you really need second level accuracy (as in deals 321 and 654) or is minute level accuracy sufficient?

Asking because the standard answer ("Use GENERATESERIES and INTERSECT") would be much tougher to do on seconds level.

Here is an alternative approach.  In your "Modified Table A"  add a calculated column:

deal_id = 
var t = 'Modified Table A'[timestamp]
var p = 'Modified Table A'[product_id]
var d = CALCULATETABLE('Modified Table B','Modified Table B'[start_date_time]<=t,'Modified Table B'[end_date_time]>=t,'Modified Table B'[product_id]=p)
return CONCATENATEX(d,'Modified Table B'[deal_id],"")

That will give you the deal ID for any order that has a deal.

However, when trying to link the tables via the deal_id field you will (not surprisingly) encounter a circular reference error.  That means it would be better to implement that calculated column in Power Query instead.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc/RDYAgDEXRVUy/SewrFNFViPuvYVuCmuhfQw630DtBMiXCqquwYGkH+GD2I8lF69Z2OlMnG4OVwVANDbe3rWrJgnDuXzmLQf967t21uVZcfnI2BoMMVz0XzmImAQlnt4bT2/Gfm2uf335fd14=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [order_id = _t, timestamp = _t, product_id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"timestamp", type datetime}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"product_id"}, #"Modified Table B", {"product_id"}, "Modified Table B", JoinKind.LeftOuter),
    #"Expanded Modified Table B" = Table.ExpandTableColumn(#"Merged Queries", "Modified Table B", {"deal_id", "start_date_time", "end_date_time"}, {"deal_id", "start_date_time", "end_date_time"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Modified Table B", "Match", each if [start_date_time]<=[timestamp] and [timestamp]<= [end_date_time] then 1 else 0),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Match", 0}}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Errors", "d", each if [Match]=1 then [deal_id] else null),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"order_id", "timestamp", "product_id", "d"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"d", "deal_id"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"deal_id", type text}})
in
    #"Changed Type1"

which then allows us to link the tables and produce the expected outcome