Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
admi
Frequent Visitor

Time between date

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

16 REPLIES 16
az38
Community Champion
Community Champion

@admi 

what do you mean "parameters" is null?

above solutions are good, but it could change the rule


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
admi
Frequent Visitor

Hi,

 

By null, I mean '0'.

 

Thanks

az38
Community Champion
Community Champion

@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 )

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
admi
Frequent Visitor

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

 

Document.DateSpeed
17/02/2020 10:24127
17/02/2020 10:24130
17/02/2020 10:24130
17/02/2020 10:24130
17/02/2020 10:24130
17/02/2020 10:24130
17/02/2020 10:24130
17/02/2020 10:2490
17/02/2020 10:2440
17/02/2020 10:240
17/02/2020 10:240
17/02/2020 10:240
17/02/2020 10:240
17/02/2020 10:240
17/02/2020 10:240
17/02/2020 10:240
17/02/2020 10:240
17/02/2020 10:250
17/02/2020 10:250
17/02/2020 10:250
17/02/2020 10:250
17/02/2020 10:250
17/02/2020 10:250
17/02/2020 10:250
17/02/2020 10:250
17/02/2020 10:360
17/02/2020 10:3622
17/02/2020 10:3645

 

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
Not applicable

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
Not applicable

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

Thanks, that works with my needs!

Document.DateSpeed
17/02/2020 10:24127
17/02/2020 10:24130
17/02/2020 10:24130
17/02/2020 10:24130
17/02/2020 10:24130
17/02/2020 10:24130
17/02/2020 10:24130
17/02/2020 10:2490
17/02/2020 10:2440
17/02/2020 10:240
17/02/2020 10:240
17/02/2020 10:250
17/02/2020 10:260
17/02/2020 10:270
17/02/2020 10:280
17/02/2020 10:290
17/02/2020 10:310
17/02/2020 10:3220
17/02/2020 10:3350
17/02/2020 10:34100
17/02/2020 10:35130
17/02/2020 10:360
17/02/2020 10:370
17/02/2020 10:380
17/02/2020 10:390
17/02/2020 10:400
17/02/2020 10:4122
17/02/2020 10:4245

 

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
Not applicable

Hi @admi 

How does this 16 come?

 

Could you please explain the logic.

 

Thanks,

Pravin

 

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
Not applicable

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
Not applicable

@admi 

 

Is it resolved your problem?

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.

 

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
Not applicable

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
Resolver II
Resolver II

 

Total Time Diff = 

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

RETURN

DATEDIFF( StartDate , EndDate , MINUTE )

 

 

Give it a thumbs up! 😃

Pragati11
Super User
Super User

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.

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.