Power BI: time management

This is how I use and calculate the time, let´s take an example, first with only the time:

power bi

To calculate in number, I will add a new column with this formula:

HOUR('table'[argument])*3600+MINUTE('table'[argument]*60)+SECOND('table'[argument])

power bi

And to convert it into number:

HOUR('table'[argument])&":"&MINUTE('table'[argument])&":"&SECOND('table'[argument])

power bi

Now I have the date and time together:

power bi

And I want to know the duration so I will calculate the difference between them to get the number of days:

power bi power bi

As I can see, it is not showing what I want. Moreover, the date is not correct and the difference of hours is not correct for the last one. To get what I need, I will change its data type and I will take advantage to change its name from “column” to “days”:

power bi power bi

This is the result:

power bi

I will add 3 new columns for the duration:

  • In hour with this formula: [argument]*24 power bi
  • In minute with this formula: [argument]*1440 power bi
  • In second with this formula: [argument]*86400 power bi
power bi

Alternatively, I can use the DATEDIFF function like that:

  • For hour with this formula: DATEDIFF([argument1],[argument2],HOUR) power bi
  • For minute with this formula: DATEDIFF([argument1],[argument2],MINUTE) power bi
  • For second with this formula: DATEDIFF([argument1],[argument2],SECOND) power bi
power bi

As I can see, comparing both formulas:

  • For hours, DATEDIFF doesn´t take in account the seconds and minutes
  • For minutes, DATEDIFF doesn´t take in account the seconds

Now, I want to show the duration in the time format, I will create a new column and use the same formula as I put for “days” and in the “data type” field, I will select “time”:

power bi

I can see that for the last row, since it is more than 24 hours, it is not showing correctly. To remediate it, I will change my formula by this one:


var calsec=[argument] // or put a formula
var shour=INT(INT(calsec/60)/60)
var smin=MOD(INT(calsec/60),60)
var ssec=MOD(calsec,60)
return
shour&":"&smin&":"&ssec // to show 2 digits: FORMAT(shour,"00")&":"&FORMAT(smin,"00")&":"&FORMAT(ssec,"00")              
              
power bi power bi

This formula will work if the [argument] is in seconds. If it is in minutes, I will use this one:


var calmin=[argument] // or put a formula
var shour=int(calmin/60)
var smin=MOD(calmin,60)
return
shour&":"&smin // to show 2 digits: FORMAT(shour,"00")&":"&FORMAT(smin,"00")             
              
power bi

Interesting Topics