Forum Discussion
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 ...
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
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
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?