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

Contributor
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)...

 




Thanks,
Arun
 

 

0 Replies