Subtract Values (Measure)

[Carsten_BI]

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.

[Anonymous]

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]

Hey yingyinr, thank you for your feedback.

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

xxxxxxxxxxxxxxxxxx	xxxxxxxxxxxxxxxxxx	xxxxxxxxxxxxxxxxxx	xxxxxxxxxxxxxxxxxx
Due Date	Customer	Item	Price
28.03.2023	110343	12700	4.474,31
28.01.2023	110343	12700	38.400,00
28.01.2023	110343	12700	38.400,00
28.01.2023	110343	12700	45.600,00
28.03.2023	110463	13196	335.264,22
28.03.2023	110463	13196	78.000,00
28.03.2023	110463	13196	51.400,00
28.01.2023	110346	12700	220.000,00
28.03.2023	110346	12700	8.200,00
28.03.2023	110346	12700	45.600,00
28.03.2023	110346	12700	38.400,00
28.03.2023	110346	12700	38.400,00
28.03.2023	110346	12700	220.600,00
28.04.2023	110287	12838	65.950,00
28.02.2023	104447	20321	8.800,00
28.08.2023	104447	13216	18.172,00
28.08.2023	104447	13216	0,01
28.07.2023	110298	13195	176.680,00
31.07.2023	110469	13389	16.500,00
31.07.2023	110461	13389	14.200,00
28.01.2023	104441	13279	280.000,00
28.02.2023	110292	12964	49.410,00
28.04.2023	593153	12599	8.150,00
28.08.2023	593154	13280	87.500,00
28.08.2023	593154	13280	5.500,00
28.08.2023	593156	13280	172.500,00
28.08.2023	593156	13280	110.000,00
28.08.2023	593158	13280	2.950,00
31.05.2023	110299	13195	2.480,00
31.03.2023	110299	13395	35.000,00
28.11.2023	110299	13395	269.000,00
31.05.2023	593156	13280	822.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]

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

[Carsten_BI]

That is so good. Still one Problem left.
I see you have cratead a suppor table to get the months.

Can you create me an example without the conversion to month? So just the exact dates? dd.mm.yyyy?

[Carsten_BI]

Push

[Carsten_BI]

Push 2