Forum Discussion
Calculate time from a datetime and between datetime values.
Hi there,
I hope someone can help me out. Our orders are invoiced based on the time the order took place, and we have different tariffs for orders that take place during the day, (partly) during the evening and/ or (partly) during the night.
What i would like to calculate in Power BI is the amount of time (in minutes) the duration of the orders took place between the from and to dates for the evening and night. I have a number of date, time and datetime values:
| Start datetime | Start date | Start time | Duration | Finish time | Evening from | Evening to | Night from | Night to |
| 18-02-20 23:59 | 17-02-20 | 23:59 | 0:02 | 0:01 | 17-02-20 19:00 | 18-02-20 08:00 | ||
| 05-01-20 21:21 | 04-01-20 | 21:21 | 0:20 | 21:41 | 04-01-20 21:30 | 05-01-20 08:00 | ||
| 21-02-20 18:54 | 20-02-20 | 18:54 | 0:08 | 19:02 | 20-02-20 19:00 | 21-02-20 08:00 | ||
| 17-01-20 09:00 | 16-01-20 | 9:00 | 1:00 | 10:00 | 16-01-20 19:00 | 17-01-20 08:00 | ||
| 17-01-20 06:25 | 16-01-20 | 6:25 | 6:35 | 13:00 | 16-01-20 18:00 | 16-01-20 23:59 | 16-01-20 00:00 | 16-01-20 08:00 |
What i would like to see is the following:
| Start datetime | Start date | Start time | Duration | Finish time | Evening from | Evening to | Night from | Night to | Time during evening | Time during night |
| 18-02-20 23:59 | 17-02-20 | 23:59 | 0:02 | 0:01 | 17-02-20 19:00 | 18-02-20 08:00 | 0:02 | |||
| 05-01-20 21:21 | 04-01-20 | 21:21 | 0:20 | 21:41 | 04-01-20 21:30 | 05-01-20 08:00 | 0:11 | |||
| 21-02-20 18:54 | 20-02-20 | 18:54 | 0:08 | 19:02 | 20-02-20 19:00 | 21-02-20 08:00 | 0:02 | |||
| 17-01-20 09:00 | 16-01-20 | 9:00 | 1:00 | 10:00 | 16-01-20 19:00 | 17-01-20 08:00 | ||||
| 17-01-20 06:25 | 16-01-20 | 6:25 | 6:35 | 13:00 | 16-01-20 18:00 | 16-01-20 23:59 | 16-01-20 00:00 | 16-01-20 08:00 | 1:35 |
Is there any way to do this in PowerBI? Unfortunately haven't been able to figure this out based on questions asked earlier, so i'm hoping someone can help!
The example pbix file to this post can be found here in case anyone finds some time to play around with this.
Cheers,
Hi
I've calculated the time during evening like so:
List.Max( {#duration(0,0,0,0),
List.Min({[Finish datetime], [Eveningtariff end datetime]})- List.Max({[Start datetime], [Eveningtariff begin datetime]}) } )
please check the file attached.
6 Replies
Hi Anonymous
I added a column that subtracted the two columns and then formatted the resulting "date" as a time.
In order for the new column to display correct, you have to switch the visual from Date Hierarchy to the "normal view".
The answer will be in the hh:mm format.
Let me know if you have any questions!
- Paul
Hi there Paul!
Thank you so much for replying and taking the time to think about the issue!
Unfortunately, this solution isn't what we are looking for. I have tried this in combination with IF statements but unfortunately I can't seem to calculate the number of minutes after a certain time and/ or before a certain time. For example, in your screenshot, the 'evening' value should be a 0 or a blank and the 'night #' value should be 1:25, since that is the time that passed between 'start time' and 'Nighttariff end datetime'. The same would go for a time within the Eveningtariff from/ to fields. I can subtract the date/ time fields like you did, but can't seem to figure out a way to discern the number of minutes up to and until a certain time and the number of minutes after that certain time.
I hope I'm making some sense! Either way, thank you for the effort so far!
Cheers
- ImkeF
Community Champion
Hi
I've calculated the time during evening like so:
List.Max( {#duration(0,0,0,0),
List.Min({[Finish datetime], [Eveningtariff end datetime]})- List.Max({[Start datetime], [Eveningtariff begin datetime]}) } )
please check the file attached.
Good morning ImkeF ,
Thank you very much for this fantastic solution. This is exactly what we were looking for and after playing around with it for a bit i understand how you came to this solution, so this is also going to help me immensly in the future. Thank you again for taking the time to look into this question!
Anonymous ImkeF's solution is exactly what we needed, but i'd like to thank you again too for taking the time to help us out!
Regards,
v-juanli-msftCommunity Support
Hi Anonymous
As tested, the [Start datetime] is not equal to [start date] +[start time],
Is this correct according to your bussiness rule?
Best Regards
Maggie