Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone.
I've got this table which shows quantities entering and exiting the warehouse, month by month.
In the first row I have the initial stock of the first day of 2021 and I want to create the column "Running Qty" that calculates the resulting stock after entries and exits:
Item | Year | Month | Data | Initial stock | Qty | RunningQty |
000BAGG000A12 | 2021 | 1 | 01/01/2021 | 2240 | -20 | |
000BAGG000A12 | 2021 | 2 | 01/02/2021 | 0 | -20 | |
000BAGG000A12 | 2021 | 3 | 01/03/2021 | 0 | -70 | |
000BAGG000A12 | 2021 | 4 | 01/04/2021 | 0 | 140 | |
000BAGG000A12 | 2021 | 5 | 01/05/2021 | 0 | -170 | |
000BAGG000A12 | 2021 | 6 | 01/06/2021 | 0 | -90 | |
000BAGG000A12 | 2021 | 7 | 01/07/2021 | 0 | -35 | |
000BAGG000A12 | 2021 | 8 | 01/08/2021 | 0 | 0 | |
000BAGG000A12 | 2021 | 9 | 01/09/2021 | 0 | 65 | |
000BAGG000A12 | 2021 | 10 | 01/10/2021 | 0 | -70 | |
000BAGG000A12 | 2021 | 11 | 01/11/2021 | 0 | -135 | |
000BAGG000A12 | 2021 | 12 | 01/12/2021 | 0 | -45 | |
000BAGG000A12 | 2022 | 1 | 01/01/2022 | 0 | -20 | |
000BAGG000A12 | 2022 | 2 | 01/02/2022 | 0 | -135 | |
000BAGG000A12 | 2022 | 3 | 01/03/2022 | 0 | -105 | |
000BAGG000A12 | 2022 | 4 | 01/04/2022 | 0 | -225 | |
000BAGG000A12 | 2022 | 5 | 01/05/2022 | 0 | -115 | |
000BAGG000A12 | 2022 | 6 | 01/06/2022 | 0 | 135 | |
000BAGG000A12 | 2022 | 7 | 01/07/2022 | 0 | -125 | |
000BAGG000A12 | 2022 | 8 | 01/08/2022 | 0 | 0 | |
000BAGG000A12 | 2022 | 9 | 01/09/2022 | 0 | 0 | |
000BAGG000A12 | 2022 | 10 | 01/10/2022 | 0 | 0 | |
000BAGG000A12 | 2022 | 11 | 01/11/2022 | 0 | 0 | |
000BAGG000A12 | 2022 | 12 | 01/12/2022 | 0 | 0 |
The ideal result would be:
I need to make a formula like:
IF (YEAR = 2021 & MONTH = 1, RunningQty = Initial Stock + Qty)
ELSE Running Qty = Running Qty of the previous month + Qty of current month.
Here is the pbix file: https://www.dropbox.com/s/z6cwp0swofjwk1q/RunningQty.pbix?dl=0
Thank you for the help!
@mtrevisiol , A new measure
Sum(Table[Initial Stock]) + Sumx(filter(all(Table), [Item] = Max(Table[Item]) && [Date] <= Max(Table[Date]) ), Table[Qty] )
or
new column =
Table[Initial Stock] + Sumx(filter((Table), [Item] = earlier(Table[Item]) && [Date] <= earlier(Table[Date]) ), Table[Qty] )
I created the new column using your formula, but I didn't get what I needed..
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |