Calculating difference in two fields from the same column

[stan255tf]

We have a list of opportunities with stages and each stage has a date associated with it based on when it was last updated. In PowerQuery, the stages are all in one column and the dates are all in one column.

How do you calculate the date difference between the two stages (New and Closed Won) when both of them are in the same column?

Note: Attaching both the PowerBI table (filtered to show only New and Closed Won stages) and the PowerQuery for reference.

[v-zhangti · Accepted solution]

Hi, @stan255tf 

Based on the data you provided, you can try the following.

Measure:

New Date = CALCULATE(MAX('Table'[CreatedDate]),FILTER(ALL('Table'),[StageName]="1. New"&&[ID]=SELECTEDVALUE('Table'[ID])))

Closed Lost Date = CALCULATE(MAX('Table'[CreatedDate]),FILTER(ALL('Table'),[StageName]="8. Cloesd Lost"&&[ID]=SELECTEDVALUE('Table'[ID])))

Difference = DATEDIFF([New Date],[Closed Lost Date],DAY)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[stan255tf]

Thanks @v-zhangti, this worked.