Forum Discussion

stan255tf's avatar
stan255tf
Regular Visitor
3 years ago
Solved

Calculating difference in two fields from the same column

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 on...
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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.

v-zhangti's avatar
v-zhangti
Community Support
3 years ago

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.