Calculating slippage

Question

Hi All,&nbsp;I have a bunch of projects and there various milestone dates in a table&nbsp;that looks something like this:&nbsp;Project IDMilestone AMilestone BRow created on127/10/2031/12/2001/1/20123/10/2031/12/2003/1/20116/10/2005/1/2109/1/20227/8/2031/11/2022/12/19223/8/2031/11/2024/12/19216/8/2007/1/2101/1/20&nbsp;I am trying to work out the slippage for each milestone, for each project,&nbsp;by finding the earliest and latest&nbsp;created instance of each&nbsp;milestone and&nbsp;then&nbsp;calculating the difference between the two.

amitchandak · Answer

Try1 = minx(filter(table,projectid = earlier(projectid) &amp;&amp; projectdate &lt;earlier(projectdate), projectdate)

2 = maxx(filter(table,projectid = earlier(projectid) &amp;&amp; projectdate &gt;earlier(projectdate), projectdate)

amitchandak · Answer

Missing syntax1 = minx(filter(table,projectid = earlier(projectid) &amp;&amp; projectdate &lt;earlier(projectdate), projectdate))

2 = maxx(filter(table,projectid = earlier(projectid) &amp;&amp; projectdate &gt;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-with-Winners/ba-p/890814https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739 https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

anonymous · Answer

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.

v-juanli-msft · Answer

Hi&nbsp;Anonymous&nbsp;
Is this problem sloved?&nbsp;
If it is sloved, could you kindly accept it as a solution to close this case?
If not, please let me know.
&nbsp;

Best Regards
Maggie

anonymous · Answer

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.&nbsp;I managed to do the below in Excel using this formula: =INDEX(B:B,MATCH(MAXIFS(D2:D7,A2:A7,A2),D:D,0)).&nbsp;I dont know if that helps explain.&nbsp;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&nbsp;

Forum Discussion

Calculating slippage

8 Replies

Related content

MoM calculation

Calculation Help

Calculate Tooltips

Hour Calculation

Calculating with Filters

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