Forum Discussion

bhmiller89's avatar
bhmiller89
Helper V
9 years ago
Solved

Calculating Multiple DateDiffs

I have some table data that shows when an Opportunity enters and leaves the "SE Queue."

 

I need to calculate total time the Opportunity spends In the SE Queue.

 

The problem is, the Opportunity enters and leaves the Queue multiple times.  I need to find a way to calculate the DATEDIFF of each change of Status and don't know how.

 

I have a primary key/unique ID for each Opportunity

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi bhmiller89,

     

    You can refer to below sample if it help for you.

     

    Table:

     

    Measure:

    Diff =
    var currState= LASTNONBLANK(Sheet4[State],[State])
    Return
    if(currState="leave",DATEDIFF(MAXX(FILTER(ALL(Sheet4),Sheet4[Opportunity ]=MAX(Sheet4[Opportunity ])&&Sheet4[Date]<MAX([Date])),[Date]),MAX([Date]),SECOND),0)


    Notice, If contain multiple "enter" date, it will get the "enter" date which nearliest the "leave" date.

     

    Create visual:

     

    Then you only need to summary the diff of the same opportunity.

     

    If above is not help, please share us some sample data.


    Regards,

    Xiaoxin Sheng

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi bhmiller89,

     

    You can refer to below sample if it help for you.

     

    Table:

     

    Measure:

    Diff =
    var currState= LASTNONBLANK(Sheet4[State],[State])
    Return
    if(currState="leave",DATEDIFF(MAXX(FILTER(ALL(Sheet4),Sheet4[Opportunity ]=MAX(Sheet4[Opportunity ])&&Sheet4[Date]<MAX([Date])),[Date]),MAX([Date]),SECOND),0)


    Notice, If contain multiple "enter" date, it will get the "enter" date which nearliest the "leave" date.

     

    Create visual:

     

    Then you only need to summary the diff of the same opportunity.

     

    If above is not help, please share us some sample data.


    Regards,

    Xiaoxin Sheng