Forum Discussion

Carsten_BI's avatar
Carsten_BI
Regular Visitor
3 years ago

Subtract Values (Measure)

Hi everyone.,

 

Lets say I have a Business and sold items to my customers. They all have a credit term and pay later.

So i have a "Sales Table" with "Item", "Payment Due Date", "Price".  - That is all I have.
Power BI gives me a Auto-Sum for "Price". Perfect. I see the Sum of open payments.

Now I want to visualize this "Backwards". So I have the Sum of "Price". 

And I want to see at which Time or Date all of my "Payments" Due will be paid.


Example:
- In January I expect 10K payments - that are still open.
- By Febuary 2K are payment due and will be paid. So only 8K are still open.

- By June another 4K will be paid, and there are only 4K still open.
So I can see how this developes over the next few months. And I can visualize this in a bar chart over time.

Given only the three fields i have.

 

Can someone give me a hint. Thanks a lot.

 

Need Help
Tips and Tricks
Tutorial Requests

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Carsten_BI ,

    You can refer the following links to get it:

    DAX for Cumulative subtraction

    Calculating Reverse Cumulative or Reverse Running Total In Power BI

     

    If the above one can't help you, could you please provide some raw data in the table  "Sales Table" (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

    How to provide sample data in the Power BI Forum

    How to Get Your Question Answered Quickly

    And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

    • Carsten_BI's avatar
      Carsten_BI
      Regular Visitor
      3 years ago

      Hey yingyinr, thank you for your feedback.


      Here is an example of the data source I have. (German Date and Number Format)

       

      xxxxxxxxxxxxxxxxxx

      		xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

      Due Date

      		CustomerItemPrice
      28.03.2023110343127004.474,31
      28.01.20231103431270038.400,00
      28.01.20231103431270038.400,00
      28.01.20231103431270045.600,00
      28.03.202311046313196335.264,22
      28.03.20231104631319678.000,00
      28.03.20231104631319651.400,00
      28.01.202311034612700220.000,00
      28.03.2023110346127008.200,00
      28.03.20231103461270045.600,00
      28.03.20231103461270038.400,00
      28.03.20231103461270038.400,00
      28.03.202311034612700220.600,00
      28.04.20231102871283865.950,00
      28.02.2023104447203218.800,00
      28.08.20231044471321618.172,00
      28.08.2023104447132160,01
      28.07.202311029813195176.680,00
      31.07.20231104691338916.500,00
      31.07.20231104611338914.200,00
      28.01.202310444113279280.000,00
      28.02.20231102921296449.410,00
      28.04.2023593153125998.150,00
      28.08.20235931541328087.500,00
      28.08.2023593154132805.500,00
      28.08.202359315613280172.500,00
      28.08.202359315613280110.000,00
      28.08.2023593158132802.950,00
      31.05.2023110299131952.480,00
      31.03.20231102991339535.000,00
      28.11.202311029913395269.000,00
      31.05.202359315613280822.669,00

       

      The sum of open payments is 3.308.199,54  

       

      No I want to see how this reduces over time and what is still left as open payment at a specific date (from todays view ). At the end i want to have something like this (just an easy example):

       

       

      Thank you in advance.

      Carsten

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Carsten_BI ,

        I created a sample pbix file(see the attachment), please check if that is what you want.

        Measure = 
VAR _selym =
    SELECTEDVALUE ( 'Date'[YearMonth] )
VAR _total =
    CALCULATE ( SUM ( 'Table'[Price] ), ALLSELECTED ( 'Table' ) )
VAR _culprice =
    CALCULATE (
        SUM ( 'Table'[Price] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            VALUE ( YEAR ( 'Table'[Due Date] ) & FORMAT ( 'Table'[Due Date], "MM" ) )
                <= VALUE ( _selym )
        )
    )
RETURN
    _total - _culprice

        Best Regards