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 'probl...
  • 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.

     

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)

       

      • gooranga1's avatar
        gooranga1
        Power Participant
        8 years ago

        Anonymous,

         

        or if you want one measure you can just use,

         

        Difference 1 = DATEDIFF(CALCULATE(min(Table10[Audit Date])), CALCULATE(max(Table10[Audit Date])),SECOND)

         

        Anonymous how would your method be used inside power bi desktop? Where does that code get pasted?