Forum Discussion

admi's avatar
admi
Frequent Visitor
6 years ago

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...
az38's avatar
az38
Community Champion
6 years ago

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's avatar
admi
Frequent Visitor
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago
    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's avatar
      admi
      Frequent Visitor
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi admi 

        How does this 16 come?

         

        Could you please explain the logic.

         

        Thanks,

        Pravin

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    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