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 ...
Need Help
Tips and Tricks
Tutorial Requests
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

    • Carsten_BI's avatar
      Carsten_BI
      Regular Visitor
      3 years ago

      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?