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,
I have 3 tables.
1)
Stock Month |
which store monthly stock on hand qty for item
Item | AccDate | Qty |
P1 | 31/01/2023 | 300 |
P1 | 28/02/2023 | 200 |
2) Stock Daily -Which store daily transaction of the item
Item | AccDate | Qty |
P1 | 02/02/2023 | -20 |
P1 | 03/02/2023 | 5 |
P1 | 04/02/2023 | 6 |
P1 | 05/02/2023 | 7 |
P1 | 06/02/2023 | 4 |
P1 | 07/02/2023 | 8 |
P1 | 08/02/2023 | -20 |
P1 | 09/02/2023 | 9 |
P1 | 10/02/2023 | 1 |
P1 | 11/02/2023 | 1 |
P1 | 12/02/2023 | -6 |
P1 | 13/02/2023 | 15 |
P1 | 14/02/2023 | 4 |
P1 | 15/02/2023 | -11 |
P1 | 16/02/2023 | 10 |
P1 | 17/02/2023 | -20 |
P1 | 18/02/2023 | 5 |
P1 | 19/02/2023 | 6 |
P1 | 20/02/2023 | 7 |
P1 | 21/02/2023 | 4 |
P1 | 22/02/2023 | 8 |
P1 | 23/02/2023 | -20 |
P1 | 24/02/2023 | 9 |
P1 | 25/02/2023 | 1 |
P1 | 26/02/2023 | 3 |
P1 | 27/02/2023 | -6 |
P1 | 28/02/2023 | 5 |
P1 | 01/03/2023 | 66 |
P1 | 02/03/2023 | -8 |
P1 | 03/03/2023 | -6 |
P1 | 04/03/2023 | -9 |
P1 | 05/03/2023 | 8 |
P1 | 06/03/2023 | 4 |
P1 | 07/03/2023 | 5 |
P1 | 08/03/2023 | 6 |
P1 | 09/03/2023 | 3 |
P1 | 10/03/2023 | 4 |
P1 | 11/03/2023 | 7 |
3) Date table
I want get daily stock balance.
where I need subtract qty of stock month for the item with qty of Stock Daily for the item. So for 28/02/2023 stock balance will 195 (200-5). on 27/02/2023 it will StockBalance of 28th with stock qty of 27th. it will be 201 (195-(-6)) as stock qty on 27th is -6. and it will reverse calculation it 31/01/2023.
Required result
Item | AccDate | Qty | StockQty |
P1 |
01/02/2023 | 10 | 175 |
P1 | 02/02/2023 | -20 | 185 |
P1 | 03/02/2023 | 5 | 165 |
P1 | 04/02/2023 | 6 | 170 |
P1 | 05/02/2023 | 7 | 176 |
P1 | 06/02/2023 | 4 | 183 |
P1 | 07/02/2023 | 8 | 187 |
P1 | 08/02/2023 | -20 | 195 |
P1 | 09/02/2023 | 9 | 175 |
P1 | 10/02/2023 | 1 | 184 |
P1 | 11/02/2023 | 1 | 185 |
P1 | 12/02/2023 | -6 | 186 |
P1 | 13/02/2023 | 15 | 180 |
P1 | 14/02/2023 | 4 | 195 |
P1 | 15/02/2023 | -11 | 199 |
P1 | 16/02/2023 | 10 | 188 |
P1 | 17/02/2023 | -20 | 198 |
P1 | 18/02/2023 | 5 | 178 |
P1 | 19/02/2023 | 6 | 183 |
P1 | 20/02/2023 | 7 | 189 |
P1 | 21/02/2023 | 4 | 196 |
P1 | 22/02/2023 | 8 | 200 |
P1 | 23/02/2023 | -20 | 208 |
P1 | 24/02/2023 | 9 | 188 |
P1 | 25/02/2023 | 1 | 197 |
P1 | 26/02/2023 | 3 | 198 |
P1 | 27/02/2023 | -6 | 201 |
P1 | 28/02/2023 | 5 | 195 |
P1 | 01/03/2023 | 66 | 129 |
P1 | 02/03/2023 | -8 | 137 |
P1 | 03/03/2023 | -6 | 143 |
P1 | 04/03/2023 | -9 | 152 |
P1 | 05/03/2023 | 8 | 144 |
P1 | 06/03/2023 | 4 | 140 |
P1 | 07/03/2023 | 5 | 135 |
P1 | 08/03/2023 | 6 | 129 |
P1 | 09/03/2023 | 3 | 126 |
P1 | 10/03/2023 | 4 | 122 |
P1 | 11/03/2023 | 7 | 115 |
I am adding multiple calculated colum in Stock Daily table but its ging circular dependency error. I do have dates table and bot the table is join with date table.
1st Calculated column
Can you please help me.
Solved! Go to Solution.
Hi,
Write these calculated column formulas
Stock = LOOKUPVALUE(Stock[Qty],Stock[AccDate],Data[AccDate],Stock[Item],Data[Item])
Date of stock availability = CALCULATE(MAX(Data[AccDate]),FILTER(Data,Data[Item]=EARLIER(Data[Item])&&Data[Stock]>0))
Cumulative qty = CALCULATE(SUM(Data[Qty]),FILTER(Data,Data[Item]=EARLIER(Data[Item])&&Data[AccDate]<=Data[Date of stock availability]&&Data[AccDate]>=EARLIER(Data[AccDate])))
Stock value = CALCULATE(sum(Data[Stock]),FILTER(Data,Data[Item]=EARLIER(Data[Item])&&Data[Stock]>0))
Bal = Data[Stock value]-Data[Cumulative qty]
Hope this helps.
Hi,
Write these calculated column formulas
Stock = LOOKUPVALUE(Stock[Qty],Stock[AccDate],Data[AccDate],Stock[Item],Data[Item])
Date of stock availability = CALCULATE(MAX(Data[AccDate]),FILTER(Data,Data[Item]=EARLIER(Data[Item])&&Data[Stock]>0))
Cumulative qty = CALCULATE(SUM(Data[Qty]),FILTER(Data,Data[Item]=EARLIER(Data[Item])&&Data[AccDate]<=Data[Date of stock availability]&&Data[AccDate]>=EARLIER(Data[AccDate])))
Stock value = CALCULATE(sum(Data[Stock]),FILTER(Data,Data[Item]=EARLIER(Data[Item])&&Data[Stock]>0))
Bal = Data[Stock value]-Data[Cumulative qty]
Hope this helps.
Thanks a ton Ashish for the solution. Its working fine for Feb month. But its not working March and Jan month.
If Bal is 210 on 28/02/2023, Qty =-10 on 01/03/2023 then Balance should be 220 ond so on
You are welcome. I do not understand what you mean. Share the PBI file, show the problem and the expected result.
Please find the pbix file
https://drive.google.com/file/d/1Z4I3hQgNFCxaIIQJByjkz4S6uj5PmJn8/view?usp=share_link
Access denied message. Show the problem there clearly.
Hi Ashish, I have given access to the report. Bal is calculating correct for Feb and Jan. But for March its not. on 28/02/2023 I have qty from Stock table is 103, to calulate back dated qty we have subtracted 103 with transacation of 28th i.e. 37. So opening bal for 27th 140.
for 1/03/2023 it should be 103-13 =90.
Thanks in Advance.
Hi,
See if revising the Cumulative Qty formula to this reolves the issue
Cumulative qty = CALCULATE(SUM(Data[Qty]),FILTER(Data,Data[Item]=EARLIER(Data[Item])&&Data[AccDate]>Data[Date of stock availability]&&Data[AccDate]<=EARLIER(Data[AccDate])))
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 |
---|---|
106 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |