Earliest date of each stage and the difference in dates between the stages

[JPSingh]

Each stage has a a number of dates historically. I am trying to get the earliest date each stage so this is all in one line like the 2nd image below. I have only got this by selecting the earliest date but this doesn't help when it comes to calculations I need to do. I ideally need to have every opportunity with the earliest date it went into that stage in one line and then do a calculation to say stage - previous stage. e.g. Engage earliest date - Diagnose earliest date. Do I need to this this in query or can we do this using DAX formula. I have sample data to view if needed but any help would be great

[JPSingh]

I have managed to resolve the issue through grouping in power query. Thank you for your help 

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Anonymous · Accepted solution]

Hi @JPSingh 

Based on your needs, I have created the following table.

In power query, hold down "Ctrl" and select all date rows, then click unpivot column.

Then you can use the following dax to get the earliest date.

Measure = MIN('Table'[Value])

Best Regards,

Jayleny

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

[JPSingh]

Thank you for this. I think the issue here is that the data I am showing is in DAX. The data is actually already unpivoted however, the issue is I am using salesforce object Opportunity and opportunity history. The Opportunity history has multiple dates under each stage e.g. I could have 4 dates under Diagnose but I need to get the earliest of this date. I have come across another solution in powerquery by grouping that is allowing me to get what I want now but the issue is the calculation of days between

[Anonymous]

Hi @JPSingh 

Can you provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.

Best Regards,

Jayleny