How to Sum Hours, Minutes, and Seconds with DAX in Power BI [h:nn:ss]
Вставка
- Опубліковано 15 жов 2024
- In this lesson, I'll show you how to sum hours, minutes, and seconds with DAX in Power BI.
Please navigate through the content below:
0:38 Agenda
0:56 DAX functions to be successful in this tutorial
1:50 Case
Content Link: drive.google.c...
___________
Highly recommended Power BI books (Affiliate links):
***The Definitive Guide to DAX: Business Intelligence for Microsoft Power BI, SQL Server Analysis Services, and Excel Second Edition: amzn.to/3ldPLpk
***DAX Patterns (Second Edition): amzn.to/3fBKdnD
***Analyzing Data with Power BI and Power Pivot for Excel: amzn.to/3fDPLOd
***Beginning DAX with Power BI: amzn.to/3fDEe1k
***Storytelling with Data: A Data Visualization Guide for Business Professionals: amzn.to/3mfPcwE
***Storytelling with Data: Let's Practice!: amzn.to/36c6Cot
***Information Dashboard Design: amzn.to/2V61o7h
___________Relevant PBI Topics
MEASURES vs CALCULATED COLUMNS in DAX: • MEASURES vs CALCULATED...
How to Use the ALL DAX Function in Power BI: • How to Use the ALL DAX...
How to Use the Filter DAX Function in Power BI [The Basics]: • How to Use the Filter ...
How to Calculate PERCENTAGES in Power BI: • How to Calculate PERCE...
How to COMBINE Tables in Power BI: • How to COMBINE Tables ...
____________
[DISCLAIMER]: Some links included in this description might be affiliate links. If you purchase a product with those links, I might receive a small commission. However, there is no additional charge to you! I appreciate your support and this will help me provide you with free content on a weekly basis.
If you like the content, feel free to buy me a cup of coffee 😀👇www.paypal.me/...
If you don't want to miss anything, subscribe to our newsletter: vizxlization.c...
___________Please follow us on:
Web: www.vizxlizati...
LinkedIn: / vizxlization
Twitter: / vizxlization
Facebook: / vizxlization
Instagram: / nestoradrianzen
Leave your comments and questions below!
Don't forget to subscribe!
Thanks so much!
#DAX #PowerBIDAX #vizxlization #hhmmss
![How to Sum Hours, Minutes, and Seconds with DAX in Power BI [h:nn:ss] - Part 2](http://i.ytimg.com/vi/Xt0FCt-h66M/mqdefault.jpg)
![Power BI Year-Over-Year Growth % Calculations [DAX]](/img/n.gif)
Thanks for presenting this so clearly. Nice job!
Sophia R, thanks for stopping by. Keep up the great work. Cheers
Time Calculation is never simple. You did a great job breaking it down so it is easy to understand. Thank you!
Glad to hear that. Cheers!
One of the best DAX video i have seen on how to handle time calculations in DAX. Thanks
Awesome! Glad to hear this.
I used this for both sum and average in one table. Seriously the best video I could ever find!!
Glad you found the content helpful, David! Keep it up!
Nestor. Can we use the new measure in a bar or column chart?. Thx
Ricardo, Thanks for stopping by. The best way to visualize this measure is in a table/matrix and card visual. The reason for that is that the data type of the measure is TEXT. I hope that helps. Cheers!
This was SOOOOOOOOOOOOOOOOOOOOOO HELPFUL! Thank you.
Glad it was helpful and thanks for your support, Ann!
Do u have the example of ,I don't want to show 2d 10h 05m 20 s, but I want 58:05:20 I want that format,it is ez to make the right adjustment by your code? Any help it would appreciate,thanks in advanced
Phanis,
Thanks for stopping by. DAX is weird! I'm going to work on a tutorial to solve this question.
In the meantime, having as a reference the code that I used in the tutorial, just remove the third and fourth variables and add the following piece of code instead: VAR HoursTotal = DaysTotal*24. Don't touch the remaining variables!
Finally in the RETURN piece, just use the following: FORMAT(HoursInt,"00")&"h, "&FORMAT(MinutesInt,"00")&"m, "&FORMAT(SecondsTotal,"00")&"s "
The result should be 89h, 20m, 53s.
Cheers!
Really helpful and exactly what I needed. Thanks, Nestor!
Glad it was helpful!👍
How can I use this on a bar chart to see evolution of time spending? Because the result is a text so it doesn’t work on a bar chart (unless I put it In decimal format but I want to see the d/h/m/s)
Hi Nestor, great video with clear instructions. Just a quick one, how would you do an average of the time with your method. Thanks
JL, Thanks for your support. For averages, you just need to use the AVERAGEX function instead of SUMX. See below for details.
VAR Total_Hours =
AVERAGEX(
Manufacturing,
HOUR(Manufacturing[Time Spent]) + MINUTE(Manufacturing[Time Spent])/60 + SECOND(Manufacturing[Time Spent])/3600
)
@@NestorAdrianzen Thank you will check it out
This video was super helpful, but I’m just wondering if you could tell me what format the time needs to be in?
My time is currently in seconds, but I can easily convert to decimal hours, or decimal minutes.
Thanks!
Edward, Thanks for checking the content out. The data is in a TIME format: hours, minutes and seconds (h:nn:ss)
Thank you for nice solution. Is there a way to sort properly if values are eg. "3d, 10h, 20m, 30s"; "22d, 10h, 20m, 30s"? 3 is lower than 22, but values are strings and alphabetically there is bad sorting order.
Another Superb video Nestor. Thank you for this detailed explanation!!!
Thanks for your support! Cheers!
Hello, Nestor, Thank you for your doing this video! The explanation is clear and precise. I have a start time and end time for Events. How do I extra the duration for each event and then total it?
Hazra Hadee, Thanks for stopping by. Have you tried the DATEDIFF function? To find the total amount, you will need to create a measure as follow. You can also change the interval to days, hours, minutes, etc.
Measure :=
SUMX (
Table,
DATEDIFF(Table[Date1], Table[Date2], DAY)
)
Good explanation and exactly what I am looking for.
Glad to hear that! Cheers 👍
Our dear professor, can you send the equation as the video is not clear to me? Thank you❤
Nestor, Thanks for such a great video! Extremely easy to follow! Subscribed!
Is there the option to remove the days and sum the hours?
I'd like to show the user the following format (45h, 35m, 00s)
Thanks in advance and keep up the good work!
James, I appreciate your feedback. There should definitely be way to do so. I'll add your suggestion to my list. Cheers!
@@NestorAdrianzen I am your new sub and I tried to follow your vid but got an error. By any chance , can you help?
Hey James! Did you find a solution to your problem? I had a job interview and they sent me a task to complete.
I have to sum hours and minutes in Power BI using the format you suggested. Thank you in advcance.
@@manoulomanoulo7 hey bud.
Yes I did! See below the code I’m using
Total Trg Hours New =
VAR TotalSeconds=SUMX('Training & Stds',HOUR('Training & Stds'[Total Trg Hours Total])*3600+MINUTE('Training & Stds'[Total Trg Hours Total])*60+SECOND('Training & Stds'[Total Trg Hours Total]))
VAR Days =TRUNC(TotalSeconds/3600/24)
VAR Hours = TRUNC((TotalSeconds-Days*3600*24)/3600)
VAR Mins =TRUNC(MOD(TotalSeconds,3600)/60)
VAR Secs = MOD(TotalSeconds,60)
return IF((Hours + (Days*24))
@@pm4bellingerj Thank you so much bro! I appreciate your help.
Thank you !!! Really well explained. I have a question about the time functions in power query. I loaded my table into power query and get an error message for durations greater than 24 hours (for example: 45:30:30). How can I solve this? I want to keep this format (Hours:Minutes:Seconds) But power query has a problem with it. when i sum my duration, I want it to be able to summarize it. For example a result like 145:29:30. ( 145 hours and 29 minutes and 30 seconds). I hope you can help me. Thanks in advance. Regards from Germany
i have the exact same problem, did you manage to solve it? tks (im still searching for a solution)
I haven't tried solving this in Power Query. Let me know how it goes!
If you want to keep hours:minutes:seconds, please review this tutorial. --> still using DAX for this analysis though. ua-cam.com/video/Xt0FCt-h66M/v-deo.htmlsi=88m-yJjQgM90Ztag
Check out this tutorial to get the result in hours:minutes:seconds --> ua-cam.com/video/Xt0FCt-h66M/v-deo.htmlsi=88m-yJjQgM90Ztag Still using DAX for this analysis.
@@NestorAdrianzen Thank you for your video. Unfortunately, the format of the result in the video is not hh:mm:ss (for example 145:29:30) 😮💨
I tried this code but it is not summing up which is having more than 24 hours duration.
Hi, If i don't want a format like d:hh:mm:ss but i'm looking for [h]:mm:ss so 89:20:53 there's something you can suggest?
Giuseppe, Thanks checking the content out.
DAX is weird! I'm going to work on a tutorial to solve this question.
Having as a reference the code that used in the tutorial, just remove the third and fourth variables and add the following piece of code instead: VAR HoursTotal = DaysTotal*24. Don't touch the remaining variables!
Finally in the RETURN piece, just use the following: FORMAT(HoursInt,"00")&"h, "&FORMAT(MinutesInt,"00")&"m, "&FORMAT(SecondsTotal,"00")&"s "
The result should be 89h, 20m, 53s.
I hope this helps!
How we can subtract two time column and get the value in hh:mm:ss
Thanks for the great presentation. it helped me a lot to understand DAX.
Subscribed too..👍
I'd like to show a line chart of Average time in HH:MM:SS format, but i was not table to do it.
I was thinking may be if there is anything can be done using DAX formula.
Thanks in advance
Please let me know if you find an answer! I´m looking for the same
Akshay, Thanks for your support. For averages, you will need to use AVERAGEX instead of SUMX in the first variable. I hope this helps. Cheers!
Mateo, Thanks for stopping by. Please see below the answer that I provided to Akshay. Cheers!
i just want in hours and not in days. what shud be modified in the code?
Hey Anubhav! Did you find the solution your problem? I have the same problem, I want to sum only in hours. Thanks.
@@manoulomanoulo7 yes I did found one
Hey folks, Thanks for checking the content out and sorry for not getting back to you sooner.
I'm going to work on a tutorial to solve this question.
In the meantime, having as a reference the code that I used in the tutorial, just remove the third and fourth variables and add the following piece of code instead: VAR HoursTotal = DaysTotal*24. Don't touch the remaining variables!
Finally in the RETURN piece, just use the following: FORMAT(HoursInt,"00")&"h, "&FORMAT(MinutesInt,"00")&"m, "&FORMAT(SecondsTotal,"00")&"s "
The result should be 89h, 20m, 53s.
Cheers!
Thank you!
Cheers!
Hey Nestor! nice video!
Could you explain how we can sum hours, minutes and seconds on this format: (h::nn:ss, e.g 94:00 h instead of 3d, 22h)
Alekos77, Thanks for stopping by. DAX is weird! I'm going to work on a tutorial to solve this question.
Having as a reference the code that used in the tutorial, just remove the third and fourth variables and add the following piece of code instead: VAR HoursTotal = DaysTotal*24. Don't touch the remaining variables!
Finally in the RETURN piece, just use the following: FORMAT(HoursInt,"00")&"h, "&FORMAT(MinutesInt,"00")&"m, "&FORMAT(SecondsTotal,"00")&"s "
The result should be 89h, 20m, 53s.
I hope this helps!
@@NestorAdrianzenthank you very much for this tutorial. But in the scenario that we want to represent the total hours and not days as mentioned above, if we delete the third and four var, in the fifth var there is the HourInt , that we have already deleted above.
Gracias Brother!
You bet!
thanks for video. Maybe you can solve my isseu too. I got my total duration from two years, but this total duration is different then I navigate it in years.
Mikayil,
Thanks for stopping by. Could you please provide more detail about what you are looking for? If you are dealing with dates, you should have a calendar table in your model and then use a date column from the calendar table to filter your data.
@@NestorAdrianzen thank for feedback. I do have calendar table. But my total duration seems to be smaller than my results in years.That is the case
Is it also possible to use this as a measure on graphs? I tried adding it to the groahs but it doesnt work.
Lalit, Thanks for stopping by. For this particular case, a CARD and TABLE/MATRIX are the appropriate charts to represent this measure. I hope this helps. Cheers!
Thank your Cooperation in learning Power bi. Namaste
By the way I subscribed your channel
Thanks and welcome to the team!
THANKS BRO, ITS QUITE HELPFUL
Glad to hear that. Cheers👍
Thanks much for the explanation!)
Glad it was helpful! Cheers!
Thanks a lot brother
Glad you found the content helpful. Cheers!
Thanks a lot
Excellent!
the Data outcome type of this measure is test, how to show graphs for that?
YKMysterious, Thanks for stopping by. One of the best visuals for this type of measures is a CARD, but you can also use a matrix/table.
When I select a year a slicer I want to show all the before years values... In the table. How to Show any requirement by using the DAX.
venkatareddy, Thanks for stopping by. For previous year calculations, you might want to use the DATEADD function. This tutorial will help. Cheers! ua-cam.com/video/6vJIAJNTdG0/v-deo.html
Could you please share this Dax script please
You're amazing! Thank you!
Ro, Glad this tutorial was helpful. Keep 🆙 the good work👍
Great video!!
Glad you enjoyed it!
Can we create weekly bar graph and show 4 week trends showing avg time taken
Andrew, Thanks for stopping by. Perhaps your are trying to find moving averages? If so, this tutorial might help. Cheers! ua-cam.com/video/jiQM93dmUek/v-deo.html
Si tuvieras que sumar turnos que tengan turnos no consecutivos por operador ,como se podría anexar a tu formula ? Saludos desde Chile.
Eladio, Gracias por revisar el contenido. El mecanismo a aplicar debe ser similar. Lo que es clave para este tipo de cálculos es entender bien los pasos y conversiones de tiempo. Saludos a los hermanos de la república de Chile👍
Thanks for sharing with very useful tricks
One suggestion if you can instead of alt_enter you can use shift+enter to create new line for DAX
It will not create sound,, hope you understand
Thanks again.
But can you please help me out regarding one query
e.g
i have three number like
300
301
301
i need to create rank ,but i need to rank like below, means my required out put is
Number rank
300 1
301 2
301 3
means i need to DAX for duplicate value
kindly help me out for this .
Thanks in advance.
Excellent! Thanks for the trick, Ashish Mohan!
@@NestorAdrianzen plzz can you solve this query
@@NestorAdrianzen plzz can you solve this query
genius! thank you
Phil, I appreciate your support and keep up the good work!
@@NestorAdrianzen no problem - I have searched for this answer in many places and no-one has approached the issue in this way - all of the other methods did not work so was thrilled to find your solution . Best regards - Boom!
very nice appreciated
Many many thanks. Keep it up, Muhammad!
Hi sir I do whatever you telling in the video but I got 29 hrs a day can u please help me in this
NuWhen I'm applying filter it shows 29 hrs.but in that particular filtering having 8:30 hrs only can u please help me
Rams, Thanks for stopping by. It's hard to tell why this is happening. My recommendation would be to start the exercise from zero again. I hope that helps!
@@NestorAdrianzen ok sir are you applying the formula in hours type column or decimal type column in power bi dax
It would be great, if I could use the sum of the hours, like make it into 199h20m05s and so on, because this is great, but I'm loosing againts the math in the report :D
Amigo tienes este tuto en español? Este tema me tiene atado de manos 😭😭😭😭
Qué tal Oscar. Lo siento. Solo lo tengo en inglés por ahora. Puedes activarle los subtítulos. Espero eso ayude. Saludos
@@NestorAdrianzen gracias amigo, eso haré, un saludo!
Our dear professor, can you send the equation as the video is not clear to me? Thank you❤
Hey Mohamed, Thanks for stopping by. Could you please share an email with your questions at nestor@quipuanalytics.com. I'll do my best to guide you. Thanks.