Help with Powerquery/Formula to calculate existing delays , new delays and out of delays

Hi All,

Hope you are doing well!.. I have a manufacturing plant data on a daily basis and specific vehicle (denoted by sno) belonging to a specific category (mmodel) that can go on a delay due to some issues...Now my Input tab has the snapshot of this data on a daily basis and denotes the vehicle (conmbination of sno and mmodel) that might be a new delay in comparison to the previous day (new delay implies that the vehicle combination dint exist on the immediate previous day to the day in comparison) ; existing delay - the same vehicle combination existed on the previous day or Off delay -the vehicle combination for the previous day doesn't exist today (or the day in consideration).. The numbers for the current day are calculated based on the vehicle entries on the current day and the immediate previous day ....The first day is the day of reference where all entries are just delays and nor off delay or new delay...The subsequent days entries are compared with the immediate previous day to get the delays ,new delays and off delay... The output tab 1 is a summary view by snapshot date and the output 2 tab is a view by master model and snapshot date...Can you please help me here..

The earliest date in my data (ascending order) would be considered as the date of reference...So in this case 03/01/2022 would be considered as the date of reference where all the unique entries (an entry is denoted by the combination of sno and mmodel) would be considered just delays...

So when I consider 03/02/2022 the common entries between 03/02/2022 and the previous day which is 03/01/2022 would be the delays which are in data (r123,S1234) ,(y123,D2123) and (W321,G345)


the entries existing in 03/01/2022 but not in 03/02/2022 would be considered as off-delays which are in my case (r3421,S1234) ,(g21q,D2123) and (E231,G345).

The entries that are completely new in 03/02/2022 but not in 03/01/2022 would be (D891,S1234) and (K1231,J1231) which are considered as new delays...

I have also added the calculation explanation file...(The entries from the previous day needs to be compared and referenced to the current day in comparison)...

So basically

1) Delay Status -The same entry is present in the immediate previous day in comparison to the current day of reference
2) Off delay-The entry from the immediate previous day is not present in the current day of reference
3) New Delay - The entry is present only in the current day of reference and not in the immediate previous day

The Input table is my actual data point. The calculation snapshot that I have shared has the details on how all the current day entries should be compared to the immediate previous day and also all the immediate previous day entries should be compared to the immediate future date (previous date +1; for example current day being march 2nd and the immediate previous day being march 1st (so all entries from march 1st should be included in march 2nd and compared with march 2nd entries (so when arriving at the status for a particular day all the current entries should be compared with the immediate previous day entries and all the previous day entries should be accounted for in the current day...).



Thanks,
Arun

I am not sure that I understand especially the portion of the formula that should identify "Off delay".
However the following seems to work for "Delay" and "New Delay" on the Input sheet:

Formula:

=IF(C2=44621,"Delay",IFERROR(IF(INDEX(C$1:C1,AGGREGATE(14,6,ROW(C$1:C1)/(A$1:A1=A2)/(B$1:B1=B2),1))=C2-1,"Delay"),"New Delay"))

Let us know if you have any questions.

Perhaps this will work:
Two columns (F:G) added to the Input sheet.
Column F shows Off delays using: =IF(COUNTIFS(C$2:C$16,C2+1)=0,"",IF(COUNTIFS(A$2:A$16,A2,B$2:B$16,B2,C$2:C$16,C2+1),"","Off Delay"))
Column G shows dates using: =IF(F2="","",SUM(C2,1))
On the Output 2 sheet both the Delay and New Delay columns are populated using: =COUNTIFS('Input '!$B$2:$B$16,$A2,'Input '!$C$2:$C$16,$B2,'Input '!$E$2:$E$16,C$1)
The Off delay column is populated using: =COUNTIFS('Input '!$B$2:$B$16,$A2,'Input '!$G$2:$G$16,$B2,'Input '!$F$2:$F$16,D$1)
On the Output 1 sheet the formula used to populate columns B:D is: =SUMPRODUCT(('Output 2'!$C$1:$E$1=B$1)*('Output 2'!$B$2:$B$14=$A2),'Output 2'!$C$2:$E$14)
Note that custom formatting ( 0;;; ) is applied on the Output sheets.
Note that I found extra trailing spaces in a lot of cells on all three sheets which will cause the formulas to yield incorrect results.
Let us know if you have any questions.