Forum Discussion
Helper IConvert minutes into days and timeformat
Hi - thanks, it works. Hopefully the VAR function works quick enough also with big data!
I have modified your DAX, to display the days or not, and hour work day has only 15 hours, so I divide through 900 instead of 1440.
WE Dauer gesamt =
var Tag= INT(Lagerbeleg[WE Dauer in Min] / 900)
var Stunde= INT(MOD(Lagerbeleg[WE Dauer in Min]; 900) / 60)
var Minute= MOD(MOD(Lagerbeleg[WE Dauer in Min]; 900); 60)
var Sekunde= INT((Lagerbeleg[WE Dauer in Min] - INT(Lagerbeleg[WE Dauer in Min])) * 60)
return
IF(Tag = 0; "";
IF (Tag > 1; Tag & " Tage "; Tag & " Tag "))
& FORMAT(Stunde; "#00")
& ":"
& FORMAT(MINUTE; "#00")
&":"&
FORMAT(Sekunde; "#00")
Hi JWE,
You can try to use belwo formula if it works on your side:
Format =
var dayNo=INT([Minutes]/1440)
var hourNo=INT(MOD([Minutes],1440)/60)
var minuteNO=MOD(MOD([Minutes],1440),60)
var secondNo=INT(([Minutes]-INT([Minutes]))*60)
return
dayNo&" day "&FORMAT(hourNo,"#00")&":"&FORMAT(minuteNO,"#00")&":"&FORMAT(secondNo,"#00")
Regards,
Xiaoxin Sheng
JWEHi - thanks, it works. Hopefully the VAR function works quick enough also with big data!
I have modified your DAX, to display the days or not, and hour work day has only 15 hours, so I divide through 900 instead of 1440.
WE Dauer gesamt =
var Tag= INT(Lagerbeleg[WE Dauer in Min] / 900)
var Stunde= INT(MOD(Lagerbeleg[WE Dauer in Min]; 900) / 60)
var Minute= MOD(MOD(Lagerbeleg[WE Dauer in Min]; 900); 60)
var Sekunde= INT((Lagerbeleg[WE Dauer in Min] - INT(Lagerbeleg[WE Dauer in Min])) * 60)
return
IF(Tag = 0; "";
IF (Tag > 1; Tag & " Tage "; Tag & " Tag "))
& FORMAT(Stunde; "#00")
& ":"
& FORMAT(MINUTE; "#00")
&":"&
FORMAT(Sekunde; "#00")
christianfcbmxPost Patron
Hello...Im Christián and I think I need something just like that...! my question is if you are able to sum that column? eg: 4 days; 8:59+2 days:3:01 geting as a result= 6days:12:00?
- JWE
Hello
yes, after I calculate the sum (like above measure, or something else) - and then I used a second measure to display days and also hours.
WE Dauer (Zeit) =
IF (
[Einl Dauer Sum] > 0;
FORMAT ( INT ( [Einl Dauer Sum] / ( 1 / 24 * 15 ) ); "#00T " )
& FORMAT ( MOD ( [Einl Dauer Sum]; ( 1 / 24 * 15 ) ); "HH:MM:SS" )
)(In this case I use 15 - because our working days are not 24h only 15h.)
- christianfcbmx
Hi Anonymous, Could you tell me how this formula should be if I only want hours and minutes?
Id appreciate your help in advance!!!
Format =
var dayNo=INT([Minutes]/1440)
var hourNo=INT(MOD([Minutes],1440)/60)
var minuteNO=MOD(MOD([Minutes],1440),60)
var secondNo=INT(([Minutes]-INT([Minutes]))*60)
return
dayNo&" day "&FORMAT(hourNo,"#00")&":"&FORMAT(minuteNO,"#00")&":"&FORMAT(secondNo,"#00")Hi JWE,
The below mentioned DAX formula works for me as well. But when I tried to add two rows,
I'm not getting the desired result because the value is taking as First or Last or Count but not Sum as mentioned below.
P.S: I'm using Impala as my Datasource.
Can you please help me to get this?
Thanks,
Akhil.
- JWE
Hi Akhil
sorry but I have no idea. I only work with the DAX in the measure and display the results without using further field functions. In this case I get the correct sum.
Cheers Jorg
Hi Anonymous Thanks for this post. Could you pls help me in making the same query bit customised or precise . .
e.g.
1 day 1:00:00 hours
1 day 00:55:70 Minutes
that is if hour is present show postfix as hours
If time is less then hour show postfix as MinutesHi! I will like know how to modify this formula so it can show seconds as well
