Subtract Qty of previous day with qty of current day-dax

[spandiar]

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

StockBalance for Month =

Var DMnth = ENDOFMONTH('Stock Daily'[AccDate])

Var HStockdate =ENDOFMONTH('Stock Month'[AccDate])

Var OpeningBal =

LOOKUPVALUE('Stock Daily'[Qty],'Stock Month'[Item],'Stock Daily'[Item],'Stock Month'[AccDate],DMnth)

Return

IF (HStockdate =DMnth, OpeningBal,BLANK())

2nd Calculated Column

Closing Balance =

Var vAccEOM =ENDOFMONTH('Stock Daily'[AccDate])

Var vAccDTCurrent = CALCULATE(SELECTEDVALUE('Stock Daily'[AccDate]))

VAR vBal='Stock Daily'[StockBalance for Month]-Stock Daily'[Qty]

var ClosingBal =IF (vAccEOM=vAccDTCurrent,vBal,BLANK())

Return

ClosingBal

3rd Calcuted Column

PreviousDay=PREVIOUSDAY('Stock Daily'[AccDate])

4th Calculated Column

Opening Balance =

Var PQty = CALCULATE(SELECTEDVALUE('Stock Daily'[Qty]),

               FILTER('Stock Daily','Stock Daily'[AccountingDate]='Stock Daily'[PreviousDay]))

Return PQty

Can you please help me. 

[Ashish_Mathur · Accepted 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.

[spandiar]

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

[Ashish_Mathur]

You are welcome.  I do not understand what you mean.  Share the PBI file, show the problem and the expected result.

[spandiar]

Please find the pbix file

https://drive.google.com/file/d/1Z4I3hQgNFCxaIIQJByjkz4S6uj5PmJn8/view?usp=share_link

[Ashish_Mathur]

Access denied message.  Show the problem there clearly.

[spandiar]

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.

[Ashish_Mathur]

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])))