Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Time duration calculation based on rows with unique identifiers

Hi all, been searching the forums for an answer for a calculation I need and so far have come up blank... 

 

I've looked through the following threads (as these seem to have solved a similar 'problem') but the solutions don't appear to work for me - or I can't get them to work correctly?

 

https://community.powerbi.com/t5/Desktop/Calculate-duration-based-on-dates-in-different-rows/m-p/58886#M24122

https://community.powerbi.com/t5/Desktop/DATEDIFF-between-Rows-and-GROUP-BY/td-p/309994 - this is seemingly a very similar problem as I'm facing and while the measure works, the values I get are not as expected. 

 

Essentially, I have rows which are distinguished by a unique id "SessionID" and the number of rows for each SessionID isn't fixed. 

 

I'm trying to formulate a DATEDIFF(?) output which looks at the highest and lowest value in the date/time column for each distinct SessionID and returns the difference. So for SessionID 2 below, I'd like an output returned of 7 secs - for SessionID 3 > 204 secs and so on... 

 

I'm new to DAX and each time I think I'm close with the expression I need, it just doesn't work... please send help! 

 

SessionID

Audit Date

2

06/09/2017 13:46:14

2

06/09/2017 13:46:20

2

06/09/2017 13:46:20

2

06/09/2017 13:46:21

3

06/09/2017 13:49:01

3

06/09/2017 13:51:10

3

06/09/2017 13:52:00

3

06/09/2017 13:52:24

3

06/09/2017 13:52:25

4

06/09/2017 13:54:26

4

06/09/2017 13:54:32

4

06/09/2017 13:54:36

4

06/09/2017 13:54:36

4

06/09/2017 13:54:36

4

06/09/2017 13:54:57

4

06/09/2017 13:55:06

4

06/09/2017 13:55:06

5

06/09/2017 13:55:23

5

06/09/2017 13:55:30

5

06/09/2017 13:55:50

5

06/09/2017 13:55:52

5

06/09/2017 13:56:09

5

06/09/2017 13:56:09

5

06/09/2017 13:56:10

5

06/09/2017 13:56:11

5

06/09/2017 13:56:11

 

Thanks in advance,

Daniel

  • gooranga1's avatar
    gooranga1
    8 years ago

    Hi Anonymous

     

    If you create 2 measures that max and min the Audit Date. then create a 3rd measure that does a datediff of these 2 measures.

     

    MinAudit = CALCULATE(min(Table10[Audit Date]))

MaxAudit = CALCULATE(max(Table10[Audit Date]))

Difference = DATEDIFF([MinAudit], [MAxAudit],SECOND)

    Then add them to a matrix with SessionId on Rows it should give you what you need.

     

7 Replies

  • gooranga1's avatar
    gooranga1
    Power Participant
    8 years ago

    Hi Anonymous

     

    If you create 2 measures that max and min the Audit Date. then create a 3rd measure that does a datediff of these 2 measures.

     

    MinAudit = CALCULATE(min(Table10[Audit Date]))

MaxAudit = CALCULATE(max(Table10[Audit Date]))

Difference = DATEDIFF([MinAudit], [MAxAudit],SECOND)

    Then add them to a matrix with SessionId on Rows it should give you what you need.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi gooranga1 thanks for this, this did indeed work. Would you (or anyone) know of a way of combining the measures to create a single measure? Rather than having 3 'bouncing' off of each other...

      Either way, this does what I need for now - would welcome any alternative solutions anyone may have.

       

      ATB, Daniel

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        If using gooranga1 method, just create the MinAudit and MaxAudit as a variable

        var MinAudit = CALCULATE(min(Table10[Audit Date]))

var MaxAudit = CALCULATE(max(Table10[Audit Date]))

        RETURN
Difference = DATEDIFF(MinAudit, MaxAudit,SECOND)

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    I have not done this specifically, but my approach would be to use MINX with a Filter on the SessionID.. Something like the following.. 

     

    MINX(KEEPFILTERS(VALUES('Data'[WW])), CALCULATE(MIN('Data'[Date])))

     

     You could then do the same with MAXX and use the output of those to in order to get the Time Difference.