validto
1 Topic
How to calculate price variance between two date ranges, with Valid From and valid To dates ?
Hi All, I have a fact table with Product ID, ValidFrom, ValidTo, & Price columns. I'd like to calculate the price difference compared to previous date range/previous validity period. Also, if there is no previous range to compare there shouldn't be any calculation, so it should give desired result as blanks. For Example : Product ID ValidFrom ValidTo Price A 1/1/2019 2/28/2019 $ 992.30 A 3/1/2019 7/10/2019 $ 984.20 A 7/11/2019 8/31/2019 $ 1,002.50 A 9/1/2019 10/31/2019 $ 986.20 B 1/1/2019 8/31/2019 $ 570.20 B 9/1/2019 10/31/2019 $ 549.80 B 11/1/2019 12/31/2019 $ 560.00 B 1/1/2020 9/30/2020 $ 549.80 Desired result: Product ValidFrom ValidTo Price PrevPrice Price Diff A 1/1/2019 2/28/2019 $ 992.30 A 3/1/2019 7/10/2019 $ 984.20 $ 992.30 =984.20-992.30 = -8.10 A 7/11/2019 8/31/2019 $ 1,002.50 $ 984.20 $ 18.30 A 9/1/2019 10/31/2019 $ 986.20 $ 1,002.50 $ (16.30) B 1/1/2019 8/31/2019 $ 570.20 B 9/1/2019 10/31/2019 $ 549.80 $ 570.20 =549.80-570.20= $ (20.40) B 11/1/2019 12/31/2019 $ 560.00 $ 549.80 $ 10.20 B 1/1/2020 9/30/2020 $ 549.80 $ 560.00 $ (10.20) Note : There are multiple products with different date ranges (Validfrom - ValidTo), it's not consistent. Trials : Created calendar date table & used active, inactive relationships with fact table on ValidFrom, ValidTo dates. But not succeeded with writing dax calculation. Tried to create index columns, starting from 0 & 1. But doesn't work as it's not continuous and changes for each product. Tried to create two separate date tables for ValidFrom & ValidTo dates, unsuccesful. Please help me with your suggestions on how to proceed and what would be the best approach to calculate this price difference either with DAX/Power Query without any performance issues as we will be dealing with large dataset here. Thanks in advance and I really appreciate your help.Solved917Views0likes2Comments