Calculating slippage

[gavb110]

Hi All,

I have a bunch of projects and there various milestone dates in a table that looks something like this:

Project ID	Milestone A	Milestone B	Row created on
1	27/10/20	31/12/20	01/1/20
1	23/10/20	31/12/20	03/1/20
1	16/10/20	05/1/21	09/1/20
2	27/8/20	31/11/20	22/12/19
2	23/8/20	31/11/20	24/12/19
2	16/8/20	07/1/21	01/1/20

I am trying to work out the slippage for each milestone, for each project, by finding the earliest and latest created instance of each milestone and then calculating the difference between the two.

[v-juanli-msft]

Hi @gavb110 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[gavb110]

Hi Maggie,

This is not solved yet, sorry for the delay. I have posted again as a follow on.

[v-juanli-msft]

Hi @gavb110

I don't find any data about 2020/10/27.

Could you explain more for this?

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

[gavb110]

Hi @v-juanli-msft, thanks for replying.

The table I posted shows how I would have resolved this in Excel, unfortunately it appears as if the column titles are not lining up with their respective columns and this may be causing some confusion.

They should read Project ID /-/ Milestone A /-/ Milestone B /-/ Row Created On /-/ Earliest Milestone A /-/ Most recent MilestoneA /-/ Days Slipped.

As you can see from the data, 27/10/2020 is the first provided date for Milestone A (using the earliest corresponding date from the Row Created On Column). 16/10/2020 is the most recently provided date for Milestone A (row created on 9/1/2020 vs 1/1/2020). Thus the difference between these two is slippage of negative 11 days (IE the data has been brought forward).

The solution provided in previous posts only looks for the earliest (lowest) and latest (highest) and wouldn't correctly identify that the slippage has reduced.

Hope that helps to explain but please do reply if you need further clarification.

[amitchandak]

Try

1 = minx(filter(table,projectid = earlier(projectid) && projectdate <earlier(projectdate), projectdate)

2 = maxx(filter(table,projectid = earlier(projectid) && projectdate >earlier(projectdate), projectdate)

[gavb110]

Hi thanks for taking the time to reply. Unfortunately I am getting an error message, too many arguments passed to the filter function. Would this solution correctly identify the earliest created entry? Just asking because later entries might have earlier dates if the deadline comes forward.

[amitchandak]

Missing syntax

1 = minx(filter(table,projectid = earlier(projectid) && projectdate <earlier(projectdate), projectdate))

2 = maxx(filter(table,projectid = earlier(projectid) && projectdate >earlier(projectdate), projectdate))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[gavb110]

Hi @amitchandak, thanks for coming back to me. Apologies I took a while to respond, I was away. I have tried your suggestion (even with the bracket in the correct place) and I notice there is a logical error as the max does not take into account date created and thus would not show negative slippage (where a date has been brought forward.

I managed to do the below in Excel using this formula: =INDEX(B:B,MATCH(MAXIFS(D2:D7,A2:A7,A2),D:D,0)).

I dont know if that helps explain.

Project ID	Milestone A	Milestone B	Row created on	Earliest Milestone A	Most Recent Milestone A	Days Slipped
1	27/10/2020	31/12/2020	01/01/2020	27/10/2020	16/10/2020	-11
1	23/10/2020	31/12/2020	03/01/2020	27/10/2020	16/10/2020	-11
1	16/10/2020	05/01/2021	09/01/2020	27/10/2020	16/10/2020	-11
2	27/08/2020	31/11/20	22/12/2019	27/08/2020	27/10/2020	61
2	23/08/2020	31/11/20	24/12/2019	27/08/2020	27/10/2020	61
2	16/08/2020	07/01/2021	01/01/2020	27/08/2020	27/10/2020	61