Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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.
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.
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
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |