Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |