Time between date

[admi]

Hi,

I need to get the time in minutes between two dates like this : 13/02/2020 17:04.00. The first and the last of my table.

I also need to calculate the time when a parameters is null. I have a column for the date and another for the parameters.

Thanks for your help

[az38]

@admi 

what do you mean "parameters" is null?

above solutions are good, but it could change the rule

[admi]

Hi,

By null, I mean '0'.

Thanks

[az38]

@admi 

how do you want o calculate this 0? as current date?

if so, try to use modificated @AlexAlberga727 solution

Total Time Diff = 

VAR StartDate = MIN( 'Table'[StartDate] )
VAR EndDate = IF(EndDate = 0, TODAY(), MAX( 'Table'[EndDate] ))

RETURN

DATEDIFF( StartDate , EndDate , MINUTE )

[admi]

Actually, I have a column for the speed and a column for the date of the data acquisiton.

Document.Date	Speed
17/02/2020 10:24	127
17/02/2020 10:24	130
17/02/2020 10:24	130
17/02/2020 10:24	130
17/02/2020 10:24	130
17/02/2020 10:24	130
17/02/2020 10:24	130
17/02/2020 10:24	90
17/02/2020 10:24	40
17/02/2020 10:24	0
17/02/2020 10:24	0
17/02/2020 10:24	0
17/02/2020 10:24	0
17/02/2020 10:24	0
17/02/2020 10:24	0
17/02/2020 10:24	0
17/02/2020 10:24	0
17/02/2020 10:25	0
17/02/2020 10:25	0
17/02/2020 10:25	0
17/02/2020 10:25	0
17/02/2020 10:25	0
17/02/2020 10:25	0
17/02/2020 10:25	0
17/02/2020 10:25	0
17/02/2020 10:36	0
17/02/2020 10:36	22
17/02/2020 10:36	45

I would like to know how much time the speed was 0. So, in this exemple it would be from 10:24 to 10:36. About 12 minutes.

Thanks for your help

[Anonymous]

Var a=calculate (min(table[date],filter(all(table),table[speed]=0))
Var b=calculate (max(table[date],filter(all(table),table[speed]=0))

Return
Datediff(a,b,minute)

Thanks
Pravin

[Anonymous]

Simply take min and max date.

Measure
Var a= calculate (min(table[date],filter(all(table),table[speed]=0))
Var b=calculate (max(table[date],filter(all(table),table[speed]=0))

Return
Datediff(a,b, minute)


Thanks
Pravin

[admi]

Thanks, that works with my needs!

Document.Date	Speed
17/02/2020 10:24	127
17/02/2020 10:24	130
17/02/2020 10:24	130
17/02/2020 10:24	130
17/02/2020 10:24	130
17/02/2020 10:24	130
17/02/2020 10:24	130
17/02/2020 10:24	90
17/02/2020 10:24	40
17/02/2020 10:24	0
17/02/2020 10:24	0
17/02/2020 10:25	0
17/02/2020 10:26	0
17/02/2020 10:27	0
17/02/2020 10:28	0
17/02/2020 10:29	0
17/02/2020 10:31	0
17/02/2020 10:32	20
17/02/2020 10:33	50
17/02/2020 10:34	100
17/02/2020 10:35	130
17/02/2020 10:36	0
17/02/2020 10:37	0
17/02/2020 10:38	0
17/02/2020 10:39	0
17/02/2020 10:40	0
17/02/2020 10:41	22
17/02/2020 10:42	45

In this case, your solution will just take the value 10:24 to 10:40 = 16 minutes. How can I do if I want that it doesn't take 10:32 10:33 and 10:34 and returns me the value = 13 minutes?

Thanks for your help!

[Anonymous]

Hi @admi 

How does this 16 come?

Could you please explain the logic.

Thanks,

Pravin

[admi]

I changed the value in the table.

So the first there is a 0 is at 10:24 and the last time you find a 0 is at 10:40. Thereby, there is 16 minutes between those two dates.

[Anonymous]

Hi @admi 

I think you want differance between min time and max time having 0

MEasure=

Var Min_time=Calculate(min(table[Date]),all(table[Date]),filter(table,table[Column]=0))

Var Max_time=Calculate(max(table[Date]),all(table[Date]),filter(table,table[Column]=0))

return

Datediff(Min_time,Max_time,minute)

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

@admi 

Is it resolved your problem?

[admi]

Document.Date		Speed
17/02/2020 10:24		127
17/02/2020 10:24		130
17/02/2020 10:24		130
17/02/2020 10:24		130
17/02/2020 10:24		130
17/02/2020 10:24		130
17/02/2020 10:24		130
17/02/2020 10:24		90
17/02/2020 10:24		40
17/02/2020 10:24		0
17/02/2020 10:24		0
17/02/2020 10:25		0
17/02/2020 10:26		0
17/02/2020 10:27		0
17/02/2020 10:28		0
17/02/2020 10:29		0
17/02/2020 10:31		0
17/02/2020 10:32		20
17/02/2020 10:33		50
17/02/2020 10:34		100
17/02/2020 10:35		130
17/02/2020 10:36		0
17/02/2020 10:37		0
17/02/2020 10:38		0
17/02/2020 10:39		0
17/02/2020 10:40		0
17/02/2020 10:41		22
17/02/2020 10:42		45

 Sorry, I'm in internship, so i was in school. My problem is not completely resolved. I need to calculate the time when my speed is 0. So here in this case, I need the time from 10:24 to 10:31= 7 min plus the time from 10:36 to 10:40 = 4 min. So a result of 11 min.

Thanks for helping me.

[AlexAlberga727]

I'm thinking of a simple way of handling this... so you're able to also filter by date. I think utilizing a calculated column to ask IF 0 then 1. Then you can create a measure to sum all of this column.

So try this - 

Create a calculated column within your data table :

Time is Zero = If ( [Speed] = 0, 1 , 0 )

Then create a Measure : 
Total time Zero = SUM ( Time is Zero )

This should create a counter for every instance time = 0. Then you're totaling the counts of 0.

Looks like you have a time stamp for every minute so that should work.

[Anonymous]

If you want column then use below dax.

Column=datediff(Date1,Date2,Minute)

If you want dynamic values as per slicers create measure.

Measure=Datadiff(Min(Table[Date1]),Min(Table[Date2]),minute)

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[AlexAlberga727]

Total Time Diff = 

VAR StartDate = MIN( 'Table'[StartDate] )
VAR EndDate = MAX( 'Table'[EndDate] )

RETURN

DATEDIFF( StartDate , EndDate , MINUTE )

Give it a thumbs up! 😃

[Pragati11]

Hi @admi ,

The difference between 2 dates in Minutes can be calculated by creating a column as follows:

DateDiffMinutes = DATEDIF(date1, date2, minute)

In the above formual, just replace "date1" & "date2" with your date columns.

Thanks.