New Measure

[Anonymous]

Hey guys, my given "Laikas" ( Time) below is in whole number format and I cant figure out new measure to return difference if the time exceeds 4:30 norm and if does not exceed 4:30 then return simply 00:00:00. Any ideas?

[amitchandak]

@Anonymous , The information you have provided is not making the problem clear to me. Can you please explain with an example.

You can compare time in Column  like

if([Laikas] >=time(4,30,0))

or measure like

if(max([Laikas]) >=time(4,30,0))

How to deal with duration -https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-using-dax

Appreciate your Kudos.

[Anonymous]

@amitchandak 

Yes, that's part of the measure, so I want to say that if Laikas(Time) exceeds 04:30:00 then return the difference between overtime and 04:30:00

[amitchandak]

@Anonymous , Evening 4 would be 16.

Row context will play role in answer

Try like

if(max([Laikas]) >=time(4,30,0) , max([Laikas]) - time(4,30,0) , 0 )

if(max([Laikas]) >=time(4,30,0) , max([Laikas]) - time(16,30,0) , 0 )

or

if(max([Laikas]) >=time(4,30,0) , datediff(max([Laikas]), time(4,30,0),minute) , 0 )
if(max([Laikas]) >=time(4,30,0) , datediff(max([Laikas]), time(16,30,0),minute) , 0 )

and a sum like

sumx(Values(Table[Indeksas]), if(max([Laikas]) >=time(4,30,0) , datediff(max([Laikas]), time(16,30,0),minute) , 0 ))

or

sumx(Values(Table[Indeksas]), if(([Laikas]) >=time(4,30,0) , datediff(([Laikas]), time(16,30,0),minute) , 0 ))

[Anonymous]

@amitchandak 

It still doesn't work, this is my following table.  first column is time in the whole number format and then second one is norm that should not be exceeded, if it exceeds this norm, then return the difference(Third column) between the two, but if it does not exceed 04:30 then return 00:00:00.

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

@amitchandak 

That's all i have at the moment. So basically first column you see with the time values, but in format as a whole number. Second column should be the drive time norm that should not be exceeded. Now the third column should do the following: If first column(klaipėda - radviliškis) exceeds second column value, then we should return the difference between the two(actual first column and second column). If first column value does not exceed second column, then we should just return actual time in the first column. 

[V-lianl-msft]

Hi @Anonymous ,

---

@Anonymous wrote:

@amitchandak 

That's all i have at the moment. So basically first column you see with the time values, but in format as a whole number. Second column should be the drive time norm that should not be exceeded. Now the third column should do the following: If first column(klaipėda - radviliškis) exceeds second column value, then we should return the difference between the two(actual first column and second column). If first column value does not exceed second column, then we should just return actual time in the first column. 

---

Sorry, I don't quite understand that. Could you share a sample pbix to let us know more about the scenario.(Please mask any sensitive data before uploading)

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.