How to calculate sum of duration for every stage filtered by ID [If missing show NOT AVAILABLE] ?

[Cortana]

I have a table like this-

 

Here, every opportunity starts from the stage named 6 Qualification and ends with the stage named 1 Closed Won. The current stage of an opportunity is the stage of the latest CreatedDate.

What I want is if I select any opportunity by filtering it should show the current stage name and how many days it is staying on that stage and also the history of the previous stages. and if there is no previous stage record it will show Not Available. For example Consider the first opportunityID - 0066g00000MPrRuAAL. It should show like this table-

6 Qualification	Not Available
5 Solution Fit	176 [58+0+11+8+34+56+9]

So is it possible? 

Sample data can be downloaded from here.

[v-yalanwu-msft · Accepted solution]

Hi， @Cortana ；
First you could create a table.

Stage = VALUES('data'[StageName])

You could create a measure as follow:

Measure = 
var _count=CALCULATE(SUM('data'[StageDays]),FILTER(ALL(data),[ID]=MAX('data'[ID])&& [StageName]=MAX('Stage'[StageName])))+0
var _maxstage=CALCULATE(LEFT(MIN('data'[StageName]),1),FILTER(ALL(data),[ID]=MAX('data'[ID])))
return IF(LEFT(MIN('Stage'[StageName]),1)>=_maxstage,IF(_count=0, "Not Available",_count))

The final show:

Or 

Measure 2 = 
var _count=CALCULATE(SUM('data'[StageDays]),FILTER(ALL(data),[ID]=MAX('data'[ID])&& [StageName]=MAX('Stage'[StageName])))+0

return IF(_count=0, "Not Available",_count)

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Cortana]

@v-yalanwu-msft 

Precious. thanks a lot.

[lbendlin]

You don't need any explicit measures for that, you get the implicit mesasures for free

[Cortana]

@lbendlin Thank you so much. Is it possible to show "Not available" where there is a blank?

[lbendlin]

Why would you want to waste all that screen real estate?

[Cortana]

I need to use that in another table. Thats why.