Filter table between yesterday at 6 AM and today at 6AM

[Syndicate_Admin]

Hello

I need to know how to filter a table with the date and time values of yesterday at 6AM, and today at 6AM (three work shifts).

Thank you!

Xavi

[Anonymous · Accepted solution]

Hi @Syndicate_Admin ,

Please try to create a measure:

Flag = 
var _start= CONVERT( TODAY()-1 &" "&TIME(6,0,0),DATETIME)
var _end= CONVERT( TODAY() &" "&TIME(6,0,0),DATETIME)
return IF(MAX('Table'[DateTime])>=_start && MAX('Table'[DateTime])<_end,1,0)

Apply it to visual-level filter, set as "is 1", the final output is shown below: 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Coriel-11]

You could try doing this in Power Query with separate date and time columns, and then add a column with the following complicated "If" statement:

if DateTime.Date(DateTime.FixedLocalNow()) > Date.From(Number.From([Date])+1) then 0 else
if DateTime.Date(DateTime.FixedLocalNow()) > Date.From(Number.From([Date])) and [Time] > Time.From(#datetime(1900,01,01,06,00,00)) then 1 else
if DateTime.Date(DateTime.FixedLocalNow()) > Date.From(Number.From([Date])) and [Time] <= Time.From(#datetime(1900,01,01,06,00,00)) then 0 else
if DateTime.Date(DateTime.FixedLocalNow()) = DateTime.Date([Date]) and [Time] > Time.From(#datetime(1900,01,01,06,00,00)) then 0 else 
if DateTime.Date(DateTime.FixedLocalNow()) = DateTime.Date([Date]) and [Time] <= Time.From(#datetime(1900,01,01,06,00,00)) then 1 else 
"error"

Then in the Power BI report, filter the 1s.

Matt