Forum Discussion
Calculating slippage
Try
1 = minx(filter(table,projectid = earlier(projectid) && projectdate <earlier(projectdate), projectdate)
2 = maxx(filter(table,projectid = earlier(projectid) && projectdate >earlier(projectdate), projectdate)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.
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-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://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/823601Hi 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