Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Hi,
By null, I mean '0'.
Thanks
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 )
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
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!
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.
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.
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.
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.
Total Time Diff =
VAR StartDate = MIN( 'Table'[StartDate] )
VAR EndDate = MAX( 'Table'[EndDate] )
RETURN
DATEDIFF( StartDate , EndDate , MINUTE )
Give it a thumbs up! 😃
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |