TimeDifference with two times

[Anonymous]

Hi,

Could please help with the calculation here?

My measure calculation is trying to subtract the times 'sdate' and 'edate' and below should be 2:10 and its displaying as 3:00 on the card.  

Is this because Ive used MAX? Ive created another column with the calculation working and displaying 2:10, but I dont know how to reference this in the measure.

[amitchandak · Accepted solution]

@Anonymous , the first way to take diff is that you create it as column

New column = [Time1] -[time2]

measure options

measure 1 = sumx(Table, Table[Time1] -Table[time2]) //you can use averageX

calculate( min(Table[Time1] ) -max(Table[time2]) , values(Table[ID])) // force row context, but Gt will wrong

refer for row context

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451

[Anonymous]

Hi @amitchandak ,

Would it still be SUMX with below?

IF(SUMX(RoomUtilisation, RoomUtilisation[New End Date]>[ParameterEndDate]),0,1)?

Would this be a different operator?

Thanks

[amitchandak]

@Anonymous , what are you try to achieve here ?

[Anonymous]

Hi @amitchandak ,

I have a couple of If statements 

 IF(SUMX(RoomUtilisation, RoomUtilisation[New End Date]>[ParameterEndDate]),1,

IF(SUMX(RoomUtilisation, RoomUtilisation[New End Date]<[ParameterEndDate]),2,

IF(SUMX(RoomUtilisation, RoomUtilisation[New End Date]<= [ParameterEndDate]),3,0

It says SUMX cannot work with type Boolean. Ive checked the formatting and all is correct.

[Anonymous]

Thankyou @amitchandak  as always 🙂