How to calculate duration in minutes between two coloms

[Anonymous]

Hello guys,

the next challenge for me is to calculate the DURATION_SOLUTION in minutes(integer).

In the current table  i have a formule for DURATION_SOLUTION, which is ENDTIME - STARTTIME. in the following image is an example (see the red markered) that the CI_NAAM Clarity has a negative duration_solution.

This incident is at 02-01-2017 started en ended two days later at 04-01-2017. So basically i expected the total duration_solution in minutes 1476 minutes.

How can i fix this ? Thank you all.

[tringuyenminh92]

Hi @Anonymous,

You could use formula:  DURATION_SOLUTION= datediff(STARTDATE,ENDDATE,Minute) + (ENDTIME - STARTTIME)

[Anonymous]

Hello @tringuyenmingh92,

Thanks for your response but unfortunatly this measure doesnt work. Check the underneath result.

[Anonymous · Accepted solution]

Hi @Anonymous,

You can try to use below formula to calculate the date diff from date and time.

Calculate column:

TimeDiff(Minute) = 
var dayDiff = DATEDIFF(DATEVALUE([StartDate]),DATEVALUE([EndDate]),MINUTE)
var hourDiff=LEFT([EndTime],2)- LEFT([StartTime],2)
var minuteDiff= RIGHT([EndTime],2)-RIGHT([StartTime],2)
return
dayDiff + hourdiff *60 + minuteDiff

In my sample, startdate and enddate are text format, enddate and startdate are number type.

Regards,

Xiaoxin Sheng

[tringuyenminh92]

Hi @Anonymous

• Please replace ; by , in your expression
• In query editor, convert data type of column Start date, End date to Date type (there will be error for some rows, so you could replace that rows error by null/or special value - plz refer topic: https://community.powerbi.com/t5/Desktop/Replace-value-in-a-field-type-date/td-p/91746)
• using isblank() method in your expression to handle rows that missed date in start date/end date