cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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 datetimeStart dateStart timeDurationFinish timeEvening fromEvening toNight from

Night to

18-02-20 23:5917-02-2023:590:020:0117-02-20 19:0018-02-20 08:00  
05-01-20 21:2104-01-2021:210:2021:4104-01-20 21:3005-01-20 08:00  
21-02-20 18:5420-02-2018:540:0819:0220-02-20 19:0021-02-20 08:00  
17-01-20 09:0016-01-209:001:0010:0016-01-20 19:0017-01-20 08:00  
17-01-20 06:2516-01-206:256:3513:0016-01-20 18:0016-01-20 23:5916-01-20 00:0016-01-20 08:00

 

What i would like to see is the following:


Start datetimeStart dateStart timeDurationFinish timeEvening fromEvening toNight fromNight toTime during eveningTime during night
18-02-20 23:5917-02-2023:590:020:0117-02-20 19:0018-02-20 08:00  0:02 
05-01-20 21:2104-01-2021:210:2021:4104-01-20 21:3005-01-20 08:00  0:11 
21-02-20 18:5420-02-2018:540:0819:0220-02-20 19:0021-02-20 08:00  0:02 
17-01-20 09:0016-01-209:001:0010:0016-01-20 19:0017-01-20 08:00    
17-01-20 06:2516-01-206:256:3513:0016-01-20 18:0016-01-20 23:5916-01-20 00:0016-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,

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Calculate time from a datetime and between datetime values.

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

6 REPLIES 6
Highlighted
Helper I
Helper I

Re: Calculate time from a datetime and between datetime values.

Hi @RascarC 

 

I added a column that subtracted the two columns and then formatted the resulting "date" as a time.

image.png

 

In order for the new column to display correct, you have to switch the visual from Date Hierarchy to the "normal view".

image.png

 

The answer will be in the hh:mm format.

 

Let me know if you have any questions!

- Paul

Highlighted
Frequent Visitor

Re: Calculate time from a datetime and between datetime values.

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

Highlighted
Super User III
Super User III

Re: Calculate time from a datetime and between datetime values.

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Highlighted
Helper I
Helper I

Re: Calculate time from a datetime and between datetime values.

Hmm. I'm not following the calculation that you need; could you explain it further? As I understand it, you want two calculated durations, but I'm not clear what fields would make up each duration.

Highlighted
Frequent Visitor

Re: Calculate time from a datetime and between datetime values.

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!

@paulq 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,

Highlighted
Community Support
Community Support

Re: Calculate time from a datetime and between datetime values.

Hi @RascarC 

As tested, the [Start datetime] is not equal to [start date] +[start time],

Is this correct according to your bussiness rule?

Capture4.JPG

 

Best Regards
Maggie

 

 

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors