topic Re: Time between date in DAX Commands and Tips

Time between date

admi — Fri, 14 Feb 2020 10:35:03 GMT

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

Re: Time between date

Pragati11 — Fri, 14 Feb 2020 15:06:23 GMT

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.

Re: Time between date

AlexAlberga727 — Sun, 16 Feb 2020 02:34:14 GMT

Total Time Diff = VAR StartDate = MIN( 'Table'[StartDate] ) VAR EndDate = MAX( 'Table'[EndDate] ) RETURN DATEDIFF( StartDate , EndDate , MINUTE )

Give it a thumbs up! 😃

Re: Time between date

az38 — Sun, 16 Feb 2020 18:46:21 GMT

admi

what do you mean "parameters" is null?

above solutions are good, but it could change the rule

Re: Time between date

Anonymous — Mon, 17 Feb 2020 07:36:19 GMT

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.

Re: Time between date

admi — Mon, 17 Feb 2020 08:20:43 GMT

Hi,

By null, I mean '0'.

Thanks

Re: Time between date

az38 — Mon, 17 Feb 2020 08:26:58 GMT

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 )

Re: Time between date

admi — Mon, 17 Feb 2020 13:16:17 GMT

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

Re: Time between date

Anonymous — Mon, 17 Feb 2020 15:24:39 GMT

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

Re: Time between date

Anonymous — Mon, 17 Feb 2020 15:25:33 GMT

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

Re: Time between date

admi — Fri, 21 Feb 2020 08:14:31 GMT

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!

Re: Time between date

Anonymous — Fri, 21 Feb 2020 10:04:41 GMT

Hi admi

How does this 16 come?

Could you please explain the logic.

Thanks,

Pravin

Re: Time between date

admi — Fri, 21 Feb 2020 14:07:29 GMT

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.

Re: Time between date

Anonymous — Tue, 25 Feb 2020 08:36:01 GMT

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.

Re: Time between date

Anonymous — Thu, 05 Mar 2020 10:07:17 GMT

admi

Is it resolved your problem?

Re: Time between date

admi — Fri, 03 Jul 2020 12:07:59 GMT

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.

Re: Time between date

AlexAlberga727 — Fri, 03 Jul 2020 14:52:13 GMT

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.