Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
2 years ago
Solved

Measure Subtracting Amounts and Adding Previous Total

Good

I'm having trouble getting one measure or another solution to the total I need. I have 2 tables, both with Amounts and another table of dates. What you would be needing would be something like Amount1-Amount2+Previous Total.

Example

DateAmount1Amount2TOTAL
01/01/24100 100
02/01/245070

80

03/01/248030130

They would help me a lot if they find a solution. Thank you!

  • amustafa's avatar
    amustafa
    2 years ago

    Assuming that your Table1 is the master table with all dates in it then you can merge the Table2 in Power Query and finally create a Total table based on the logic Amount1 - Amount2 + LastRowTotal. Download the sample excel files and Power BI form my shared folder link below.

     

    https://1drv.ms/f/s!Aq3n-sopiGyqgokSrrq0UV8vR-OraQ?e=3WqQeP

     

    If I answered your question, please mark this thread as accepted and Thums Up!
    Follow me on LinkedIn:
    https://www.linkedin.com/in/mustafa-ali-70133451/

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous

     

    Here is my testing.

     

    My sample:

     

     

    1. Merge two tables in the Power Query editor

     

     

    2. Create two calculated columns as follow

     

    column = 'table'[Amount1] - 'table'[Amount2]
    TOTAL = 
VAR _previoustotal = CALCULATE(SUM('table'[column]), FILTER('table', 'table'[Date] < EARLIER('table'[Date])))
RETURN
'table'[column] + _previoustotal

     

     

     

    Is this the result you expect?

     

    If I've misunderstood you, please provide the data and structure of the two tables. Please remove any sensitive data in advance.

     

    Best Regards,
    Community Support Team _Yuliax

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • amustafa's avatar
    amustafa
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Assuming that your Table1 is the master table with all dates in it then you can merge the Table2 in Power Query and finally create a Total table based on the logic Amount1 - Amount2 + LastRowTotal. Download the sample excel files and Power BI form my shared folder link below.

     

    https://1drv.ms/f/s!Aq3n-sopiGyqgokSrrq0UV8vR-OraQ?e=3WqQeP

     

    If I answered your question, please mark this thread as accepted and Thums Up!
    Follow me on LinkedIn:
    https://www.linkedin.com/in/mustafa-ali-70133451/

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous

     

    Here is my testing.

     

    My sample:

     

     

    1. Merge two tables in the Power Query editor

     

     

    2. Create two calculated columns as follow

     

    column = 'table'[Amount1] - 'table'[Amount2]
    TOTAL = 
VAR _previoustotal = CALCULATE(SUM('table'[column]), FILTER('table', 'table'[Date] < EARLIER('table'[Date])))
RETURN
'table'[column] + _previoustotal

     

     

     

    Is this the result you expect?

     

    If I've misunderstood you, please provide the data and structure of the two tables. Please remove any sensitive data in advance.

     

    Best Regards,
    Community Support Team _Yuliax

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.