Calculate elapsed time

[Syndicate_Admin]

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.

[v-weiyan1-msft · Accepted solution]

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.

[lbendlin]

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-...

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/1447...