Forum Discussion

yasbui's avatar
yasbui
Frequent Visitor
8 years ago
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 ...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

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
yasbui's avatar
yasbui
Frequent Visitor
8 years ago

Thank you so much! Exactly what I was looking for! :smileyvery-happy: