Forum Discussion
Subtracting rows
Hi folks,
I'm new here and quite new to power BI.
I have two tables:
1. Items with purchase date & Quantity
| ItemNo | PurchaseDate | Quantity | Index |
| 854 | 20/03/2019 | 32 | 2 |
| 854 | 30/04/2019 | 480 | 3 |
| 854 | 07/05/2019 | 16 | 4 |
| 854 | 27/01/2019 | 203 | 1 |
| 346 | 20/05/2019 | 3 | 2 |
| 346 | 23/07/2019 | 2 | 3 |
| 346 | 22/01/2019 | 1 | 1 |
| 346 | 08/08/2019 | 1 | 4 |
2. Sales forecast
| ItemNo | Forecast |
| 854 | 350 |
| 346 | 2 |
I would like to add Balance column to my table 1, that will subtract forecast from quantity, starting with the oldest PurchaseDate. My desired outcome is this:
| ItemNo | PurchaseDate | Quantity | Index | Balance |
| 854 | 20/03/2019 | 32 | 2 | 0 |
| 854 | 30/04/2019 | 480 | 3 | 365 |
| 854 | 07/05/2019 | 16 | 4 | 16 |
| 854 | 27/01/2019 | 203 | 1 | 0 |
| 346 | 20/05/2019 | 3 | 2 | 2 |
| 346 | 23/07/2019 | 2 | 3 | 2 |
| 346 | 22/01/2019 | 1 | 1 | 0 |
| 346 | 08/08/2019 | 1 | 4 | 1 |
How can I achieve this with simple measures?
Thanks in advance.
3 Replies
Hi,
How have you arrived at the numbers in the Balance column? What is the logic?
SUM of Quantity minus Forecast = Balance is my logic, but it would only work if the table was grouped by item.
Hi RadoslawM
My output is a bit different from yours.
Create columns
clc_quan = CALCULATE(SUM('Table 1'[Quantity]),FILTER(ALLEXCEPT('Table 1','Table 1'[ItemNo]),'Table 1'[PurchaseDate]<=EARLIER('Table 1'[PurchaseDate]))) clc_quan = CALCULATE(SUM('Table 1'[Quantity]),FILTER(ALLEXCEPT('Table 1','Table 1'[ItemNo]),'Table 1'[PurchaseDate]<=EARLIER('Table 1'[PurchaseDate]))) Column = IF([clc_for]=BLANK(),0,[clc_quan]-[clc_for])Please tell me if i misunderstand anything.
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
