Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RascarC
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
ImkeF
Super User
Super User

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
v-juanli-msft
Community Support
Community Support

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

 

 

ImkeF
Super User
Super User

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

RascarC
Frequent Visitor

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,

Anonymous
Not applicable

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

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

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.