FIFO Cost of Sales and Closing Inventory Calculation

[PhyuLayKhine333]

Hi All BI Experts,

I am beginner to BI and tried many methods in order to get the below result and also google about this problem but i did not get the correct answer and have no ideas how to use the relevant formula to get this below result.  The calculation is about if the current cummulative sale qty with the current product name is less than current cummulative purchase qty which is filter by date , then using the current number . If not, then using another row quantity . Its basis is just fifo inventory valuation method. 

Any suggestion of fomula to get the beblow result?

Thanks and Regards,

Sales Table

Date	Product Name	Quantity	Cummulative Qty
10/10/2022	A	10	10
10/12/2022	A	3	13
10/12/2022	A	2	15
10/13/2022	A	1	16
10/10/2022	B	22	22
10/11/2022	B	10	32
10/12/22	B	3	35

Purchase Table

Date	Product Name	Quantity	Cummulative Quantity	Cost
9/12/2022	A	10	10	110
9/30/2022	A	4	14	130
10/1/2022	A	6	20	150
9/1/2022	B	20	20	210
9/2/2022	B	20	40	250
9/20/2022	B	10	50	230

Then i would like get the table like this;

Date	Product Name	Quantity	Cummulative Qty	Cost (only show text like this)	Cost (show total calculation number)	Closing Inventory Qty	Closing Inventory Value(only show text like this	Closing Inventory Value(show total calculation number)
10/10/2022	A	10	10	(110*10)	1100	10	(130*4)+(150*6)	1420
10/12/2022	A	3	13	(130*3)	390	7	(130*1)+(150*6)	1030
10/12/2022	A	2	15	(130*1)+(150*1)	280	5	(150*5)	750
10/13/2022	A	1	16	(150*1)	150	4	(150*4)	600
10/10/2022	B	22	22	(210*20)+(250*2)	920	28	(250*18)+(230*10)	6800
10/11/2022	B	10	32	(250*10)	2500	18	(250*8)+(230*10)	4300
10/12/22	B	3	35	(250*3)	750	15	(250*5)+(230*10)	3550

[KatyaK]

Hi dear, did you solve the task? I have the same problem

[amitchandak]

@PhyuLayKhine333 , if you need a column

Cumm Qty = sumx(filter(table, [Product Name] = earlier([Product Name] ) && [Date] <= earlier([Date] ) ),[Quantity])

Cumm Cost = sumx(filter(table, [Product Name] = earlier([Product Name] ) && [Date] <= earlier([Date] ) ),[Quantity]*[Cost])

measure without date table

Cumm Qty = sumx(filter(allselected(table), [Product Name] = max([Product Name] ) && [Date] <= max([Date] ) ),[Quantity])

Cumm Cost = sumx(filter(allselected(table), [Product Name] = max([Product Name] ) && [Date] <= max([Date] ) ),[Quantity]*[Cost])

example measure with date table, joined with date of your tables

Cumm Cost = calculate( sumx(Table,[Quantity]*[Cost]), filter(all(Date), Date[Date]<= Max(Date[DAte])))

Cumm Cost = calculate( sumx(Table,[Quantity]*[Cost]), filter(allselected(Date), Date[Date]<= Max(Date[DAte])))

[PhyuLayKhine333]

sir ,i just want to get each row cost not the cummulative cost.

[PhyuLayKhine333]

Sir, I have try your formula and get this result which is not cummulative and only show the total cost of sales for each product and the cost of sales result is not correct . 

I am using 

Cumm Cost = sumx(filter(Purchase, [Product Name] = earlier([Product Name] ) && [Date] <= earlier([Date] ) ),[Quantity]*purchase [Cost]) 

The result;

Date	Product Name	Quantity	Cumm Qty	Cumm Cost
Monday, October 10, 2022	A	10	10	2520
Wednesday, October 12, 2022	A	3	15	2520
Wednesday, October 12, 2022	A	2	15	2520
Thursday, October 13, 2022	A	1	16	2520
Monday, October 10, 2022	B	22	22	11500
Tuesday, October 11, 2022	B	10	32	11500
Wednesday, October 12, 2022	B	3	35	11500

The result i would like to get;

te	Product Name	Quantity	Cummulative Qty	Fifo Cost of sales	Closing Inventory Qty	Closing Inventory Value
10/10/2022	A	10	10	1100	10	1420
10/12/2022	A	3	13	390	7	1030
10/12/2022	A	2	15	280	5	750
10/13/2022	A	1	16	150	4	600
10/10/2022	B	22	22	920	28	6800
10/11/2022	B	10	32	2500	18	4300
10/12/22	B	3	35	750	15	3550

[Anonymous · Accepted solution]

HI @PhyuLayKhine333,

I'd like to suggest you use summarize function to summary table records to calculate the total values in the variable, then you can use the iterator function to aggregate the variable table result to apply second aggregations.

Cumm Cost =
VAR summary =
    SUMMARIZE (
        Purchase,
        [Product Name],
        [Date],
        "Total", [Quantity] * purchase[Cost]
    )
RETURN
    SUMX (
        FILTER (
            summary,
            [Product Name] = EARLIER ( Purchase[Product Name] )
                && [Date] <= EARLIER ( Purchase[Date] )
        ),
        [Total]
    )

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

[PhyuLayKhine333]

 I have 2 tables which is sales and purchase table. Which table name do i need to use for Cumm Cost. 

  Cumm Qty = sumx(filter(table, [Product Name] = earlier([Product Name] ) && [Date] <= earlier([Date] ) ),[Quantity])

  Cumm Cost = sumx(filter(table, [Product Name] = earlier([Product Name] ) && [Date] <= earlier([Date] ) ),[Quantity]*[Cost])

[ravi_609]

Hey @PhyuLayKhine333 Is this solved?