Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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
Maggie
Community 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.
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.