topic Calculating difference in two fields from the same column in DAX Commands and Tips

Calculating difference in two fields from the same column

stan255tf — Wed, 02 Aug 2023 01:36:24 GMT

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.

Re: Calculating difference in two fields from the same column

v-zhangti — Wed, 02 Aug 2023 06:16:26 GMT

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.

Re: Calculating difference in two fields from the same column

stan255tf — Thu, 03 Aug 2023 01:46:43 GMT

Thanks v-zhangti, this worked.