like FIFO, but with details of when the material quantity stock is in

[WilsonHianusa]

For example, i have this 2 table

transactionTable =
DATATABLE(
"materialNumber", STRING,
"postingDate", DATETIME,
"quantity", INTEGER,

{
{"LGXXX", "2024-09-06", 1},
{"LGXXX", "2024-09-07", 2},
{"LGXXX", "2024-09-07", -2},
{"LGXXX", "2024-07-07", 5},
{"LGXXX", "2024-09-07", -4},
{"LGXXX", "2024-09-08", 1},
{"LGXXX", "2024-09-09", -1},
{"LGYYY", "2024-09-08", 5},
{"LGYYY", "2024-09-09", 4},
{"LGZZZ", "2024-09-08", 3},
{"LGZZZ", "2024-09-09", 2}
}
)

^ i have this table,

and this table too

stockTable =
DATATABLE(
"materialNumber", STRING,
"quantity", INTEGER,
"amountInLC", INTEGER,

{
{"LGXXX", 3},
{"LGYYY", 6},
{"LGZZZ", 1}

}
)

so it works like a fifo method (first in first out) in transactionTable, where if the quantity is positive , it mean stock in, and if quantity is negative , it mean stock out

from these 2 tables, i want to make a new table where the new table shows the remaining quantity, this remaining quantity value can easily be gained from stockTable for each materialNumber, but in this new table, i also want to get the value of remaining quantity with the date the stock is in , which is similar to fifo method.

so for example with the tables above example, it would produce this table

{"LGXXX", "2024-09-08", 1 },
{"LGXXX", "2024-09-07", 2 },
{"LGYYY", "2024-09-09", 4 },
{"LGYYY", "2024-09-08", 2 },
{"LGZZZ", "2024-09-09", 1 }

i have read radacad's posts about fifo method, and im a bit confused, it seems that its goal is different than what im trying to do

does anyone understand my question?

[some_bih]

@WilsonHianusa ok for format.

for part stock table, there is no reason why it shoul not be in column format the same as transaction table? stock table is not like transaction table or ...

[WilsonHianusa]

Hi again!, stock table is automatically updated through some programs everyday, and the date is automatically today / current day, so it does not need to have date column , even if it does have date column, it will be something like this

if it does have a date column, do you have a solution ?

[some_bih]

Hi @WilsonHianusa this is great news for your model in power bi.

Just use these two separate table as there have the same structure so you can append these tables, chek link for example. After that create measure for your reports and new tables.

Are you familiar with append table query?

[WilsonHianusa]

hi again! , i think you misunderstood my sentences,

yes, im very familiar with append table, but the example above i give u is not the question im asking about, i was just explaining it further about the stock table, regarding the date column.

okay then , lets say i have a date column in stock table, so then the case would be like this,

with the existence of transactionTable and stockTable, i want a power bi dax query that can make a new table like 'expectedTable'

[some_bih]

Hi @WilsonHianusa great. Your new table / Expected table is not trouble to be created.

Are example data in your picture provide exact outpu in expected table or just dummy data?

I would like to recreate it so I need to know could I reconcile it with your table.

[WilsonHianusa]

it provided exact output  like the expected table, you can try to do it

[some_bih]

Hi @WilsonHianusa 

I created enclosed file, but I could not get your numbers in expected table.

Please give example for some materialnumber how you get numbers in your example

[WilsonHianusa]

Hi!, sorry for late replies,

how i get my materialNumbers,  from which table?

for both 'transactionTable' and 'stockTable' , i do cron job everyday , so the data in that table is automatically updated everday , including materialNumber and other data

for 'expectedTable' , i hope to use power bi to create this table to get the materialNumber and all the other data (i havent been able to know how to make this yet)

[some_bih]

Hi @WilsonHianusa ok for your processes, still how you get data in 'expectedTable' by example is not known to me, so please check enclosed file if it is ok for you.

[WilsonHianusa]

the point of my question is to make an "expectedTable" with the help of "stockTable" and "transactionTable"

all you did is just appending both stockTable and transactionTable , its not what i want , as ive said on my question, what i want is 

so it works like a fifo method (first in first out) in transactionTable, where if the quantity is positive , it mean stock in, and if quantity is negative , it mean stock out

from these 2 tables, i want to make a new table where the new table shows the remaining quantity, this remaining quantity value can easily be gained from stockTable for each materialNumber, but in this new table, i also want to get the value of remaining quantity with the date the stock is in , which is similar to fifo method.

so for example with the tables above example, it would produce this table ...

[some_bih]

Hi @WilsonHianusa I understand you need FIFO, that is why I asked you what is format for your dates and you replay it is DD-MM-YYYY. Still, I do not know why in your transaction table date 07.07. 2024 (with positive sign), after 07.09.2024?

This is the reason I asked for your calculation logic for some materialNumber.

To get something in power bi we should need logic I could not figure out it just based on picture with data as shown. Do you have some other details in table like row number or...
This could help in explaining your model.

[WilsonHianusa]

Hi! , yes the format for my date is DD-MM-YYYY

Still, I do not know why in your transaction table date 07.07. 2024 (with positive sign), after 07.09.2024?

This is the reason I asked for your calculation logic for some materialNumber.

ok, ill try to explain, i think i have tried to explain it in my first post, maybe its just not clear enough

on why, 07.07.2024 (with positive sign) after 07.09.2024 ,
the date 07.07.2024 means that its the date that the stock gets in the warehouse, thus
both the 07.07.2024 and 07.09.2024 is not correlated, they are both independent of each other, its just the date that the stock gets in the warehouse

This is the reason I asked for your calculation logic for some materialNumber.

materialNumber is similar as 'product name' or 'product type' kind of thing. its the product name of which the product gets in the warehouse

is there anything that you still think unclear, feel free to ask , thank you 

[some_bih]

@WilsonHianusa what is your date format, like MM-DD-YYY or DD-MM-YYYY?
stockTable is with error as there is 3 columns but only 2 data? typo or ....

[WilsonHianusa]

Hi! date format is DD-MM-YYYY,

as for stockTable its only 2 column, materialNumber and quantity,

because date in stockTable , the date is always current day / today