Forum Discussion
Post Patronspecific time from datetime field
Hi,
from the below sample data, i need to display the values on the visual only for the time period 10:00 AM to 04:00 PM:
| DateTime | Predicted Value | Actual Value | Variation |
9/8/2020 6:10:00 AM | 100 | 96 | 4 |
| 9/6/2020 8:10:00 AM | 99 | 105 | 6 |
| 9/7/2020 10:20:00 AM | 101 | 97 | 4 |
| 9/8/2020 12:02:00 PM | 98 | 90 | 8 |
| 9/6/2020 3:32:00 PM | 96 | 98 | 2 |
| 9/8/2020 04:00:00 PM | 100 | 95 | 5 |
| 9/8/2020 06:02:00 PM | 82 | 75 | 7 |
| 9/7/2020 08:02:00 PM | 54 | 50 | 4 |
| 9/6/2020 11:11:00 PM | 30 | 28 | 2 |
Any suggestion to acheive this really appreciated.
Thanks
jitpbi ,
In power query you can use Time.Hour
Time.Hour([Datetime])
if you want to remove data you can now filter between 10 to 16
if Time.Hour([Datetime]) >=10 and Time.Hour([Datetime]) <=16 then 1 else 0
4 Replies
- amitchandak
Super User
jitpbi , Better you create a time column or use [Datetime].time in place ot time
time = [DateTime].time
Create formula for all measure you need
example
Predicted value M =calculate(Sum(Table[Predicted Value ]) , filter(Table, Table[time]>=time(10,0,0) && Table[Time]<=Time(16,0,0)))Actual Value M =calculate(Sum(Table[Actual Value]) , filter(Table, Table[time]>=time(10,0,0) && Table[Time]<=Time(16,0,0)))
Or Create a time table. Join with table and use that as a filter. You can column of 24 Hours and use a range too
https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/
jitpbiHi amitchandak ,
Just thinking to do it in the edit query, if we can select from the datetime field for the time 10 AM to 4 PM. So the data will be loaded for 10 AM to 4 PM only. Can you please help me to do this in power query.
Thanks
- amitchandak
jitpbi ,
In power query you can use Time.Hour
Time.Hour([Datetime])
if you want to remove data you can now filter between 10 to 16
if Time.Hour([Datetime]) >=10 and Time.Hour([Datetime]) <=16 then 1 else 0