Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Everyone,
thank you for your help. I really appreciate it.
I have an issue I would like to submit to you. I started working on it but one is very tricky. I have two columns start_time and end_time. the goal is to separate them between the duration day (between 7am and 9pm) and duration night (between 9pm and 7 am). they are all in second. I succeeded for most but the issue arose when the end time finishes next day. Do you have a quicker solution? Thank you.
you can find the powerbi report here.
https://1drv.ms/u/s!Aqd0HyltQcBtii8kQChgPgYJ9K0R?e=I5NcTG
thank you for your help
Solved! Go to Solution.
Thank you for your reply. it was really helpful. is it possible to divide morning into morning 7am to 2:30pm and afternoon 2:30pm to 9pm?
I made a modification but could not make it work well.
https://1drv.ms/u/s!Aqd0HyltQcBtijNF6faJUen12jsY?e=HQdYRn
Thank you
@ivannye , One of the DateTime, Start or end has to drive it like start
Duartion Night =if(Hour([StartTime]) >= 7 && Hour([StartTime]) <=21,0,1)
end
Duartion Night =if(Hour([EndTime]) >= 7 && Hour([EndTime]) <=21,0,1)
Hi Thank you for your quick reply. duration day should look at start_time and returns the number of second between if start time falls between 7 to 9PM. for example if start time is 1/01/2020, 8:45:00pm and end time if 1/01/2020, 21:10:00pm, the duration day is 15minutes*60 and duration night is 10minutes*60.
@ivannye , Try two new columns like
Duration Night =
Switch(true() ,
Hour([StartTime]) <= 21 && Hour([EndTime]) >=21 , datediff(date(year([start time]), month([start time]), day([start time])) + time(21,0,0), [Endtime], second),
Hour([StartTime]) <= 7 && Hour([EndTime]) >=7 , datediff([start time], date(year([Endtime]), month([Endtime]), day([Endtime])) + time(7,0,0), second),
datediff([start time], [Endtime] , second)
)
Duration Day =
Switch(true() ,
Hour([StartTime]) <= 21 && Hour([EndTime]) >=21 , datediff([start time], date(year([Endtime]), month([Endtime]), day([Endtime])) + time(21,0,0), second) ,
Hour([StartTime]) <= 7 && Hour([EndTime]) >=7 ,datediff(date(year([start time]), month([start time]), day([start time])) + time(7,0,0), [Endtime], second),
datediff([start time], [Endtime] , second)
)
Thank you for your reply. above you wrote starttime and endtime. do you mean start_time and end_time?
@ivannye , yes, You start_time and End_time columns. Please choose the column's name as in your file. Diff is in seconds
Thank you. Here is the result below.
As you can see the solution on both column duration_day and duration_night are the same. durationday and durationnight are manual calculations
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |