Forum Discussion
Calculate elapsed time
Good afternoon
I'm transferring information to the power bi I have the opening and closing dates, it would be possible to show the elapsed time,
as in the format dd, hh:mm:ss , and in some cases the closing date is empty.
I use the following formula in excel I won't put it in the power bi.
Hi Syndicate_Admin ,
The logic of your calculation seems to be that
when closing date is empty, elapsed time = current date - opening date.
When not empty, elapsed time = closing date - opening date.
Based on my understanding, here is the data I created from the sample you provided.Please try the following code to create Calculated Column.
TIEMPO(dd, hh:mm:ss) = VAR Sec = IF('Table'[FECHA CIERRE] = BLANK(), DATEDIFF('Table'[FECHA APERTURA], TODAY(), SECOND), DATEDIFF('Table'[FECHA APERTURA], 'Table'[FECHA CIERRE], SECOND) ) VAR Days = INT(Sec / 86400) VAR Hours = INT(MOD(Sec, 86400) / 3600) VAR Minutes = INT(MOD(Sec, 3600) / 60) VAR Remainder = MOD(Sec, 60) RETURN FORMAT(Days, "00") & ", " & FORMAT(Hours, "00") & ":" & FORMAT(Minutes, "00") & ":" & FORMAT(Remainder, "00")Result is as below.
Please correct me if I misunderstood your needs.
Best Regards,
Yulia Yan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi Syndicate_Admin ,
The logic of your calculation seems to be that
when closing date is empty, elapsed time = current date - opening date.
When not empty, elapsed time = closing date - opening date.
Based on my understanding, here is the data I created from the sample you provided.Please try the following code to create Calculated Column.
TIEMPO(dd, hh:mm:ss) = VAR Sec = IF('Table'[FECHA CIERRE] = BLANK(), DATEDIFF('Table'[FECHA APERTURA], TODAY(), SECOND), DATEDIFF('Table'[FECHA APERTURA], 'Table'[FECHA CIERRE], SECOND) ) VAR Days = INT(Sec / 86400) VAR Hours = INT(MOD(Sec, 86400) / 3600) VAR Minutes = INT(MOD(Sec, 3600) / 60) VAR Remainder = MOD(Sec, 60) RETURN FORMAT(Days, "00") & ", " & FORMAT(Hours, "00") & ":" & FORMAT(Minutes, "00") & ":" & FORMAT(Remainder, "00")Result is as below.
Please correct me if I misunderstood your needs.
Best Regards,
Yulia Yan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
