Hi Community,
I have an opportunity history table, similar to the below, and I am trying to calculate the average length of time opportunities spend in each stage. I want to plot the average time, in days, on a time series with the categorical stages as my x-axis. I've created a separate table of the each opportunity stage and a corresponding order, so the stages plot in sequence on x-axis and will connect that reference table to my Opportunity['StageName'] table to accomplish this.
| OpportunityId | StageName | Date |
| AA | Identification | 1/1/23 |
| AA | Qualification | 2/1/23 |
| AA | Cultivation | 3/1/23 |
| AA | Verbal Commitment | 5/1/23 |
| BB | Identification | 2/1/23 |
| BB | Qualification | 3/1/23 |
| BB | Verbal Commitment | 7/1/23 |
I don't necessarily care about time in each stage on an individual opportunityId level, rather, I want to show that, on average, opportunities spend 30 days in Identification and so on from there. Opportunities can skip stages, though.
Any help in creating a DAX measure in support of this would be greatly appreciated. Thank you!
