Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, I am trying to find the difference in contractprice for same contract numbers. I have created 2 index columns and using the below dax query for calculated column but it is giving me wrong difference.
Solved! Go to Solution.
For the second row in your image, it's calculating as follows.
diff = 18,951.00 - (sum of [ContractPrice] where [index1] = 2 )
RETURN
18,951.00 - ( 18,951.00 - 2,485,870.83 ) = 2,485,870.83
I think you want something more like this:
price diff =
VAR prev =
LOOKUPVALUE (
contractlines[ContractPrice],
contractlines[ContractNumber], contractlines[ContractNumber],
contractlines[index], contractlines[index1]
)
RETURN
IF ( ISBLANK ( prev ), BLANK (), contractlines[ContractPrice] - prev )
For the second row in your image, it's calculating as follows.
diff = 18,951.00 - (sum of [ContractPrice] where [index1] = 2 )
RETURN
18,951.00 - ( 18,951.00 - 2,485,870.83 ) = 2,485,870.83
I think you want something more like this:
price diff =
VAR prev =
LOOKUPVALUE (
contractlines[ContractPrice],
contractlines[ContractNumber], contractlines[ContractNumber],
contractlines[index], contractlines[index1]
)
RETURN
IF ( ISBLANK ( prev ), BLANK (), contractlines[ContractPrice] - prev )