Hi @simoncotterill ,
You can try the following custom columns. In your environment, you may need to change “Date.From([EventTime])” in the equation to today.
= Table.AddColumn(#"Changed Type with Locale1", "Custom", each if (Time.From([EventTime]) >= #time(17, 0, 0) and Date.From([EventTime]) = Date.From([EventTime])) or
(Time.From([EventTime]) <= #time(5, 30, 0) and Date.From([EventTime]) = Date.AddDays(Date.From([EventTime]), 0))
then "InRange" else "OutOfRange")
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
, type logical is part of the whole code and not just of the custom column. If you paste the whole code to a blank query and click the wrench next to added custom code step, you will see formula that's just for that column
let
DateToday =
Date.From(
DateTimeZone.RemoveZone( DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), 8 ) ) ),
//Power BI service is set to utc so the time needs to converted to your local timezone, replace 8 with UTC offset
DateYesterday = Date.AddDays(DateToday, - 1),
DateTime1 = DateTime.From(Number.From(DateYesterday) + Number.From(#time(17,0,0))), //date and time cannot be added directly thus they're converted to numbers first
DateTime2 = DateTime.From(Number.From(DateToday) + Number.From(#time(5,30,0)))
in [EventTime] >= DateTime1 and [EventTime] <=DateTime2
Hi @simoncotterill ,
You can try the following custom columns. In your environment, you may need to change “Date.From([EventTime])” in the equation to today.
= Table.AddColumn(#"Changed Type with Locale1", "Custom", each if (Time.From([EventTime]) >= #time(17, 0, 0) and Date.From([EventTime]) = Date.From([EventTime])) or
(Time.From([EventTime]) <= #time(5, 30, 0) and Date.From([EventTime]) = Date.AddDays(Date.From([EventTime]), 0))
then "InRange" else "OutOfRange")If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hello @simoncotterill
just my curiosity, does simple row filtering not work for your case?
after you split column between date and time as you mentioned in your first post, then just do time filter the row with between condition and using OR operator.
Thank you.
Please see sample query below
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fVZLbis5DLzKIOtHPFGkSMk7fS8R5P7XmKKdzARx2osIDbfLLJFVxby/v6W0Z0qilNLZOLKS7JRo9pQcb1ZL6e3PW7K/nP9mvP6n3pK+ffx5fxu7lmNjUFtNSAcL9aWLfLvwOWPN035CWe7QlJqhVhTcB0dTKgVVW01p4qHqc9VPaKlJ0tiF8pZJqqg/xDdxO2sO7qV4uYC2nVbXfmh1BnTVDuq4cPM5y5ruiccl4ZNT4hyEJw7plAYIK9qjDsJySVh52uZaSOcCgOemLr1R8bOz2uk92RXhZly5ZbLpTFoa09g+aTZrZbtn13PdYUyQB7jOhUONZhAuBzPFg42fhJlvandsnTJaO4Oc6w7GlUYV/MAUbjI4r+VX2FbQYh2hp44eS25UtQ/ikSbbEW29XGHBksGOgS2hCtu0IAjqaL2iwS3/xtnv2By/LbZo+zbCSJxaB+ey9oEah+wtV1jMFJprTtGa6FUhOZ/DTXiAbi7ryqxrVhGaflCXrdFYNVOuOvbZTbPU67oC6ag+SqY0FmVckTK84bjvXr/VrV+CDM6QIXwUDYOW74IMN+OP62/Y9piv5cZTwbRmR6/ggzEkk5uU7jbs7H2FbW2vPUalzq2R9ixU5+gE1052P2Jbr7AP1SWh8Es4yaiixZTCQFBr+Y1zSV9Y3NfCeyOwcFEDjFgiuIB/1obfMn9iYe1koasZ812NGkfdHr4Aj6e0+Y6tmMJI4fnyEEjEIiVwbaht5xV24Hor7JfvdeHcmG9cOsxoT9n6HXtQxlvkcnihIwQilyu64CCfX2CdXc5B4jBKIOdKRq/yimDffWUr6j/n+63uQoVUUdJDVxqRFffVh//MXt43QjJS3UKYcKOGJsM/KTRZXmFj+lAEvhnz7eVRt9vDg6/7XEMBMV+NXkGTO7AY+n1GoH6JNe8I/7bIHCbWPMO/ULaMU8qZi1d90asCBaXg3EIgsEGOuktjVYDHpyb9f+wtfcVzx5cs2mz5sXslWuW4yMDDsUsodl8vOpWG9UpasQWrD2RPxkV76rysX0AVgTOHVloQJoQxjXqHsnpN+LQgQS1fQFfObMaFHOFPmg7ytTg2Pj48cyNdG19AHctDFYE88jqk+4DwlEULGdlREVv5CoruosP7rsZQxSrUIjGkh58fyrjuML5gEa4eE0L9+8YW9L16yPQFNETn4dsV+L2iQ4kOjoFX5Wk4fuNHxJWRHf8QbJi1QMFIY3QYu1dqmysX+O/oT2j+SrhYNzNCpoSU4SOxqIrDQHg9EfabPKAb6Vd3xtaRBiEgmwiqVmL2IiNzmas9Qf/zrIcYQ8PR5j3u16R9Hluxyy9Vv6D9Hghhu3uvsARD/mXFEscF9BIqmPxhJPIsSApoAB1miCMPz/BB3mP6E7S8fXz8Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, EventTime = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"EventTime", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Filter", each let
DateToday =
Date.From(
DateTimeZone.RemoveZone( DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), 8 ) ) ),
//Power BI service is set to utc so the time needs to converted to your local timezone, replace 8 with UTC offset
DateYesterday = Date.AddDays(DateToday, - 1),
DateTime1 = DateTime.From(Number.From(DateYesterday) + Number.From(#time(17,0,0))), //date and time cannot be added directly thus they're converted to numbers first
DateTime2 = DateTime.From(Number.From(DateToday) + Number.From(#time(5,30,0)))
in [EventTime] >= DateTime1 and [EventTime] <=DateTime2, type logical)
in
#"Added Custom"
, type logical is part of the whole code and not just of the custom column. If you paste the whole code to a blank query and click the wrench next to added custom code step, you will see formula that's just for that column
let
DateToday =
Date.From(
DateTimeZone.RemoveZone( DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), 8 ) ) ),
//Power BI service is set to utc so the time needs to converted to your local timezone, replace 8 with UTC offset
DateYesterday = Date.AddDays(DateToday, - 1),
DateTime1 = DateTime.From(Number.From(DateYesterday) + Number.From(#time(17,0,0))), //date and time cannot be added directly thus they're converted to numbers first
DateTime2 = DateTime.From(Number.From(DateToday) + Number.From(#time(5,30,0)))
in [EventTime] >= DateTime1 and [EventTime] <=DateTime2
Helpful resources
Power BI Monthly Update - August 2025
Check out the August 2025 Power BI update to learn about new features.
Fabric Community Update - August 2025
Find out what's new and trending in the Fabric community.
