Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Can you post some sample data?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |