Forum Discussion
Solved
How to calculate difference between two row values
Hi
I would like to calculate the difference of lost sales between rows but only with stores with the same number only (there is a grouping by date), I'm trying to use a calculated column but I'm not sure how to identify rows with the same id. The table is shown below:
Any help would be appreciated.
Thanks.
Hi yasbui
If you have a date column ..you can try using this Calculated Column
Column = VAR First_Date = CALCULATE ( MIN ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[store] ) ) VAR FirstValue = CALCULATE ( SUM ( Table1[lostsales_collected] ), ALLEXCEPT ( Table1, Table1[store] ), Table1[Date] = First_Date ) VAR Second_Date = CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[store] ) ) VAR SecondValue = CALCULATE ( SUM ( Table1[lostsales_collected] ), ALLEXCEPT ( Table1, Table1[store] ), Table1[Date] = Second_Date ) RETURN FirstValue - SecondValue
2 Replies
Hi yasbui
If you have a date column ..you can try using this Calculated Column
Column = VAR First_Date = CALCULATE ( MIN ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[store] ) ) VAR FirstValue = CALCULATE ( SUM ( Table1[lostsales_collected] ), ALLEXCEPT ( Table1, Table1[store] ), Table1[Date] = First_Date ) VAR Second_Date = CALCULATE ( MAX ( Table1[Date] ), ALLEXCEPT ( Table1, Table1[store] ) ) VAR SecondValue = CALCULATE ( SUM ( Table1[lostsales_collected] ), ALLEXCEPT ( Table1, Table1[store] ), Table1[Date] = Second_Date ) RETURN FirstValue - SecondValueThank you so much! Exactly what I was looking for! :smileyvery-happy:
