MAX function not working as expected

Hi, Let me give a background to the problem I am facing: I have a fact table which is a snapshot table (it basically stores multiple modifications of same OrderID). For Modification date, we have...