Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only

View My Drafts

Back to discussions
Expand all | Collapse all

subtract between two rows of the same column

  • 1.  subtract between two rows of the same column

    Silver Contributor
    Posted Feb 18, 2020 11:45 AM

    ​Hello, I am trying to subtract between two rows of the same column, and put the result in another column.

    thank you for any help


    ------------------------------
    Nadjim
    ------------------------------


  • 2.  RE: subtract between two rows of the same column

    Top Contributor
    Posted Feb 18, 2020 02:35 PM
    Hello,

    You have to create two index columns in Power Query, sort the data first. An index starting from 0 and an index starting from 1. Then in a formula, you have to use the EARLIER function to perform the calculation in a calculated column.

    Example for TotalSales (As I'm in France, I use ";" but I think you just have to replace it by ","):

    SalesVariation = SALES[SalesAmount]-CALCULATE(SUM(SALES[SalesAmount]);FILTER(SALES;SALES[Index.1]=EARLIER(SALES[Index])))

    Good luck,


    ------------------------------
    Jonathan CHANAL
    Chef de Projet Digital / Data Analyst
    Grenoble
    ------------------------------

    Original Message


  • 3.  RE: subtract between two rows of the same column

    Silver Contributor
    Posted Feb 19, 2020 08:01 AM
    ​Hello,

    Yes it's working. Thank you so much.

    ------------------------------
    Nadjim Idrs
    ------------------------------

    Original Message


  • 4.  RE: subtract between two rows of the same column

    Top Contributor
    Posted Feb 19, 2020 09:53 AM
    Ok very good, have a nice day,

    ------------------------------
    Jonathan CHANAL
    Chef de Projet Digital / Data Analyst
    Grenoble
    ------------------------------

    Original Message


  • 5.  RE: subtract between two rows of the same column

    Top Contributor
    Posted Feb 19, 2020 10:11 AM
    The same is possible in a dynamic scenario (on a visual, for example). Rather than using EARLIER() you can use variables or TREATAS() to grab the "previous"  values for your computation.

    ------------------------------
    Lutz
    ------------------------------

    Original Message


  • 6.  RE: subtract between two rows of the same column

    Posted Feb 16, 2021 03:03 AM
      |   view attached

    Hi Jonathan,

    Thank you very much for your help.

    I have an issue I am struggling with and if you'll find few minutes to have a look, will be really thankful.

    Basically I have a table with Products, Dates and Values (attached pbix file - Table1). I need to substract values from same column (Value column) for filtered product.
    I created a calculated column (attached pbix file - Table2) but it works only when I have a table with a single product, also, I tried to add an index but it's not filtering properly the dates.
    Below I'll attach the .pbix file and few snips with the expected outcome for Table 1.






    ------------------------------
    Sturza Roman
    Power BI
    ------------------------------

    Attachment(s)

    pbix
    Test.pbix   69 KB 1 version
    Original Message


  • 7.  RE: subtract between two rows of the same column

    Top Contributor
    Posted Feb 16, 2021 04:13 AM
    Hello,

    You can use the same formula as above (Calculated Column) and use ALLEXCEPT in the filter to filter all values except for the Product value. In my example, it should be :

    SalesVariation = SALES[SalesAmount]-CALCULATE(SUM(SALES[SalesAmount]);FILTER(ALLEXCEPT(SALES, SALES[Product]);SALES[Index.1]=EARLIER(SALES[Index])))

    I hope it helps, I don't have much time to help today :(

    Have a nice day,

    ------------------------------
    Jonathan CHANAL
    Chef de Projet Digital / Data Analyst
    MCSA Power BI & Excel
    ------------------------------

    Original Message


  • 8.  RE: subtract between two rows of the same column

    Top Contributor
    Posted Feb 16, 2021 04:20 AM
      |   view attached
    Edit: in attachment, my solution.

    I hope it helps ;)

    ------------------------------
    Jonathan CHANAL
    Chef de Projet Digital / Data Analyst
    MCSA Power BI & Excel
    ------------------------------

    Attachment(s)

    pbix
    Test JCH.pbix   28 KB 1 version
    Original Message


  • 9.  RE: subtract between two rows of the same column

    Posted Feb 16, 2021 05:13 AM
    Genius :), Merci beaucoup Jonathan. I owe you one!

    ------------------------------
    Sturza Roman
    Power BI
    ------------------------------

    Original Message


  • 10.  RE: subtract between two rows of the same column

    Top Contributor
    Posted Feb 16, 2021 05:56 AM
    Perfect! Thank you ;)

    ------------------------------
    Jonathan CHANAL
    Chef de Projet Digital / Data Analyst
    MCSA Power BI & Excel
    ------------------------------

    Original Message


  • 11.  RE: subtract between two rows of the same column

    Posted Mar 23, 2021 10:54 AM
    Hi, I've tried this approach which seems to suit my needs, but I get all the time this error: EARLIER/EARLIEST refers to an earlier row context which doesn't exist.
    A help will be VERY appreciated :)



    ------------------------------
    Tomasz Konrad
    ------------------------------

    Original Message


  • 12.  RE: subtract between two rows of the same column

    Posted Mar 24, 2021 04:35 AM
    I found out! This formula is for a calculated column :)

    ------------------------------
    Tomasz Konrad
    ------------------------------

    Original Message


  • 13.  RE: subtract between two rows of the same column

    Posted Nov 10, 2021 06:40 AM
    Edited by Sturza Roman Nov 10, 2021 06:54 AM
      |   view attached
    Hi Jonathan,

    I hope you are doing well.

    Thank you very much for your valuable posts, I have a bit strange scenario and get stuck on how to manage it in BI.
    If you'll have a few minutes, I'll be very thankful if you'll have a look over it, basically, I need to subtract values from the same column, but only when the value is less than 0 based on a value from a different column. I attached the pbix file and the expected outcome column in Excel.

    Thank you in advance. 

    ------------------------------
    test

    Sturza Roman
    Power BI
    ------------------------------

    Attachment(s)

    pbix
    Test.pbix   29 KB 1 version
    Original Message


  • 14.  RE: subtract between two rows of the same column

    Top Contributor
    Posted Nov 11, 2021 02:10 AM
    Edited by Jonathan CHANAL Nov 11, 2021 02:11 AM
    Hello,

    As a best practice, please start your model with a date table and retrieve a date instead of week column. All your calculations with time intelligence functions will work properly with this technic. For your specific case, you need to use ALLEXCEPT in a calculated column to perform the calculation or a SUMX in a DAX function. But first, date table! It will be easier to perform calculations after that.

    Have a nice day,

    P.S.: @Roman, please start a new topic for your question next time

    ------------------------------
    Jonathan CHANAL
    Chef de Projet Digital / Data Analyst
    MCSA Power BI & Excel
    ------------------------------

    Original Message


  • 15.  RE: subtract between two rows of the same column

    Posted Jan 11, 2022 11:30 AM
    Hi Jonathan,

       I have a rows of time in date and hrs format in a column,i want subtract the below date with above date and populate hours in a new column.Its a start and stop time.ANy inputs appreciated.

    thx
    Jag

    ------------------------------
    Jag kris
    ceo
    ------------------------------

    Original Message