Calculating Multiple DateDiffs

[bhmiller89]

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

[Anonymous · Accepted solution]

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

[Greg_Deckler]

Can you post some sample data?