topic Re: specific time from datetime field in DAX Commands and Tips

specific time from datetime field

jitpbi — Tue, 08 Sep 2020 07:23:07 GMT

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

Re: specific time from datetime field

amitchandak — Tue, 08 Sep 2020 07:32:06 GMT

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/

Re: specific time from datetime field

jitpbi — Tue, 08 Sep 2020 09:49:21 GMT

Hi 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

Re: specific time from datetime field

amitchandak — Tue, 08 Sep 2020 10:24:25 GMT

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

Re: specific time from datetime field

jitpbi — Wed, 09 Sep 2020 12:37:09 GMT

Hi amitchandak ,

I am gettiing another challenege after republishing the report to service, where i replaced with the existing dataset, the "hour" column is not showing in the table. Also getting the data for entire time as before not for the time (>=10 &<= 16) i filtered in the power query. The same file in desktop was working fine where hour column is there in the table and data is filtered for the time >=10 &<= 16.

Can you please suggest what would be the reason and how to resolve this.

Thanks