Calculate if delivered time in between the range of Slot Date and Slot time in Power BI

[maqboolk51]

Hi I'm new to Power BI and I have a question if any one can help.

I basically want to know if my order was delivered in between the Date and Slot time provided by the customer. 

Id	Order Created Date Time	Delivered Date Time	Slot Date	Slot Time
1943069	01/09/2023 02:34	01/09/2023 15:10	01/09/2023	09:00 - 12:00
1943070	01/09/2023 02:39	01/09/2023 20:00	01/09/2023	18:00 - 21:00
1943071	01/09/2023 02:42	01/09/2023 16:50	01/09/2023	15:00 - 18:00
1943073	01/09/2023 02:48	01/09/2023 11:06	01/09/2023	09:00 - 12:00
1943074	01/09/2023 02:51	04/09/2023 13:44	04/09/2023	12:00 - 15:00
1943075	01/09/2023 02:53	04/09/2023 13:43	04/09/2023	12:00 - 15:00
1943078	01/09/2023 03:00	01/09/2023 20:39	01/09/2023	18:00 - 21:00
1943079	01/09/2023 03:03	02/09/2023 13:46	02/09/2023	15:00 - 18:00
1943080	01/09/2023 03:05	01/09/2023 13:57	01/09/2023	15:00 - 18:00
1943082	01/09/2023 03:09	01/09/2023 13:51	01/09/2023	12:00 - 15:00

[ThxAlot]

Very straightforward, #date & #time produces #datetime

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdJbCsQgDAXQrYjfHZqnVbdSuv9tjHaEIQkz9Ks00NMbr+eZsQlDaXnLgDu0nYA4AXUWO0LtCGY0X1oHSK+ENJ752hZ3QOTcHwjmF47D+uEILYeBE3LpStfI6UpXLceRq44bAcrTZSVwegeWL8ddxIxmOlqcWk4jx5Hjp5zbDNif+6zCtfOnCn9RBndHIZOumNHvKqq/KINz+w9Oj4fNVopcixwGzp7d9QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, #"Order Created Date Time" = _t, #"Delivered Date Time" = _t, #"Slot Date" = _t, #"Slot Time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Order Created Date Time", type datetime}, {"Delivered Date Time", type datetime}, {"Slot Date", type date}, {"Slot Time", type text}}),
    Check = Table.AddColumn(#"Changed Type", "Check", each let span = List.Transform(Text.Split([Slot Time], " - "), Time.From) in [Slot Date] & span{0} <= [Delivered Date Time] and [Delivered Date Time] <= [Slot Date] & span{1})
in
    Check

[slorin]

Hi

another solution

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdJbCsQgDAXQrYjfHZqnVbdSuv9tjHaEIQkz9Ks00NMbr+eZsQlDaXnLgDu0nYA4AXUWO0LtCGY0X1oHSK+ENJ752hZ3QOTcHwjmF47D+uEILYeBE3LpStfI6UpXLceRq44bAcrTZSVwegeWL8ddxIxmOlqcWk4jx5Hjp5zbDNif+6zCtfOnCn9RBndHIZOumNHvKqq/KINz+w9Oj4fNVopcixwGzp7d9QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, #"Order Created Date Time" = _t, #"Delivered Date Time" = _t, #"Slot Date" = _t, #"Slot Time" = _t]),
ChType = Table.TransformColumnTypes(Source,{{"Order Created Date Time", type datetime}, {"Delivered Date Time", type datetime}, {"Slot Date", type datetime}}),
AddOnTime = Table.AddColumn(ChType, "AddOnTime", each 
   [Delivered Date Time] >= [Slot Date] + Duration.From(Text.Start([Slot Time],5)) 
   and
   [Delivered Date Time] <= [Slot Date] + Duration.From(Text.End([Slot Time],5)),
   type logical)
in
AddOnTime

with "Slot Date" = type datetime and not type date

Stéphane

[m_dekorte]

Hi @maqboolk51 

Give this a go

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdJbCsQgDAXQrYjfHZqnVbdSuv9tjHaEIQkz9Ks00NMbr+eZsQlDaXnLgDu0nYA4AXUWO0LtCGY0X1oHSK+ENJ752hZ3QOTcHwjmF47D+uEILYeBE3LpStfI6UpXLceRq44bAcrTZSVwegeWL8ddxIxmOlqcWk4jx5Hjp5zbDNif+6zCtfOnCn9RBndHIZOumNHvKqq/KINz+w9Oj4fNVopcixwGzp7d9QY=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, #"Order Created Date Time" = _t, #"Delivered Date Time" = _t, #"Slot Date" = _t, #"Slot Time" = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Order Created Date Time", type datetime}, {"Delivered Date Time", type datetime}, {"Slot Date", type date}}),
    AddOnTime = Table.AddColumn(ChType, "OnTime", each 
        ( Date.From([Delivered Date Time]) = [Slot Date] ) and 
        [   t= Time.From([Delivered Date Time]), 
            s= List.Transform( Text.Split([Slot Time], " - "), Time.From ), 
            r= s{0} <= t and s{1} >= t
        ][r], type logical
    )
in
    AddOnTime

returns this result

I hope this is helpful