I've applied several transformation steps inside Power Query, obtaining a such result:
but I need to obtain a such result, before using some DAX logics to obtain the final KPIs:
The override operation occurs after the related end of the validity period.
The override sections are optional, up to five ones.
I thought to detect the previous row to understand the passage from the old stock value to the new stock value to override. I cannot adding an index, I need to handle some millions of rows (I've expanded the validity period to split in date + time, hour for hour), perhaps I need to search the previous row respect to the WS_SITE and the VALIDITY_DATETIME less one hour.
Suggestion 1: Share your data in text-tabular format so that people trying to help can copy the table contents easily and run some tests. Just paste the table contents here, or share an excel file or a pbix with the data
Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.