Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys
I have an excel file with 2 dates and over PB I could get the result expected (the diff in minutes and then convert into hours, minutes, and seconds)...but that new calculated column can not be switched from text format (the way it is now) to date or time or number or anything to sum, get averages etc. PB doesnt allows it I guess... anyone could help me out!!!!
And this is the formula I used in power BI to get the calculated column:
Duración (7*24) = var hourNo=INT([TotalminutesDiff(7*24)]/60)
var minuteNO=MOD('EXTR-INC&WO-Genérico v4 0 (Chil'[TotalminutesDiff(7*24)],60)
var secondNo=INT(([TotalminutesDiff(7*24)]-INT([TotalminutesDiff(7*24)]))*60)
return
FORMAT(hourNo,"#000")&":"&FORMAT(minuteNO,"#00")&":"&FORMAT(secondNo,"#00")
Hi @christianfcbmx,
Did you try the formula like this?
Duración (7*24) = VAR hourNo = INT ( [TotalminutesDiff(7*24)] / 60 ) VAR minuteNO = MOD ( 'EXTR-INC&WO-Genérico v4 0 (Chil'[TotalminutesDiff(7*24)], 60 ) VAR secondNo = INT ( ( [TotalminutesDiff(7*24)] - INT ( [TotalminutesDiff(7*24)] ) ) * 60 ) RETURN TIME ( hourNo, minuteNO, secondNo )
Best Regards,
Dale
...still an error :(,,,,thank you Thanks Dale anyways!
Hi @christianfcbmx,
The format 00003:07:48 isn't a valid numeric format in Power BI. Maybe you can use the Text mode to display and use another approach to calculate the numeric calculation.
About the format.
format = VAR allseconds = DATEDIFF ( [Start], [End], SECOND ) VAR days = INT ( allseconds / 24 / 60 / 60 ) VAR hours = MOD ( INT ( allseconds / 60 / 60 ), 24 ) VAR minutes = MOD ( INT ( allseconds / 60 ), 60 ) VAR seconds = MOD ( allseconds, 60 ) RETURN days & "d: " & hours & "h: " & minutes & "m: " & seconds & "s"
About the total calculation. You can sum the numeric values fisrt, then format to d:h:m:s.
Best Regards,
Dale
This is bacicly the result I´m looking for:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |