Inventory Qutantity wise Againg Report FIFO

[unais]

Team,

Please advise how can get create Invontry quantiy wise Stock againg Report in FIFO model.

Same table having Selling and Buying information.

Refer below link same Data source here sepcified to calculate FIFO selling value , where i need to create Againg report for current stock , how long this stocks available.

https://radacad.com/dax-inventory-or-stock-valuation-using-fifo#comment-349084

[amitchandak]

@unais , refer this

https://radacad.com/dax-inventory-or-stock-valuation-using-fifo

[Anonymous]

To start with, post the list of tables with field names and the relationship between the tables in your data model. Or atleast, give some sample data from your table showing all the fields with few lines of data. 

Without this, no one will be able answer your question to the point. May be people will direct you to some blog post and you will have to spend considerable time reading, understanding, modifying and adapting the concepts to match your requirements. 

[unais]

here the source https://almaznetae-my.sharepoint.com/:u:/g/personal/unais_almaz_net_ae/EfFeVU6Y-rxHnpfcjm4oXJoBE2y_d...

[Anonymous]

Hi,

Assuming that you have the following table in your data model...

Table Name: Table1

Symbol	type	Units	date	value per unit	Total Value
AAA	buy	5	01 January 2014	$100.00	$500.00
AAA	buy	10	01 May 2016	$200.00	$2,000.00
AAA	sale	6	01 August 2016	$500.00	$3,000.00
AAA	buy	20	01 January 2017	$800.00	$16,000.00
AAA	sale	22	01 May 2017	$700.00	$15,400.00
BBB	buy	13	05 June 2016	$300.00	$3,900.00
BBB	sale	13	01 July 2016	$120.00	$1,560.00
CCC	buy	55	01 July 2018	$100.00	$5,500.00
DDD	buy	4	01 January 2018	$1.10	$4.40
DDD	buy	4	02 January 2018	$1.20	$4.80
DDD	buy	4	03 January 2018	$1.30	$5.20
DDD	buy	4	04 January 2018	$1.40	$5.60
DDD	buy	4	05 January 2018	$1.50	$6.00
DDD	sale	13	06 January 2018	$2.50	$32.50
DDD	buy	4	07 January 2018	$1.70	$6.80
DDD	buy	4	08 January 2018	$1.80	$7.20
DDD	buy	4	09 January 2018	$1.90	$7.60
DDD	sale	8	10 January 2018	$3.50	$28.00
DDD	sale	6	11 January 2018	$4.50	$27.00

Note: I have deleted all calculated columns from your pbix file.

Step1: Add the following calculated column to your table.

StockInHandToday =
VAR CurrentSymbol = Table1[Symbol]
VAR CurrentDate = Table1[date]
VAR CurrentLineQty = Table1[Units]
VAR TotalPurchases =
    SUMX (
        FILTER ( Table1, Table1[Symbol] = CurrentSymbol && Table1[type] = "buy" ),
        Table1[Units]
    ) + 0
VAR TotalSales =
    SUMX (
        FILTER ( Table1, Table1[Symbol] = CurrentSymbol && Table1[type] = "sale" ),
        Table1[Units]
    ) + 0
VAR PurchasesAfterCurrentDate =
    SUMX (
        FILTER (
            Table1,
            Table1[Symbol] = CurrentSymbol
                && Table1[type] = "buy"
                && Table1[date] > CurrentDate
        ),
        Table1[Units]
    ) + 0
VAR CurrentStock =
    MAX (
        0,
        MIN ( TotalPurchases - TotalSales - PurchasesAfterCurrentDate, CurrentLineQty )
    )
VAR Result =
    IF ( Table1[type] = "Buy", CurrentStock, 0 )
RETURN
    IF ( Result = 0, BLANK (), Result )

Step 2: Add another calculated column for Ageing Days.

StockAgeingInDays = 
VAR CurrentDate = TODAY()
VAR BuyDate = Table1[date]
VAR Age = ROUND(CurrentDate-BuyDate,0)
RETURN Age & " Days"

Sample Output:

Disclaimer: It is assumed that your data's granularity is at day level. If your actual data is at document level with multiple purchase or sales documents on the same day, there is a chance that the stock-in-hand will get repeated for every document on the same day and your aggregation will show a wrong stock figure. In that case, you have to modify the formula accordingly or use a table with day-level granularity.

[unais]

hi Sreenath,

Thanks for your update.

I had updated source files, with small changes Added the Same Date with the Same item multiple transactions then again values are coming wrong..

https://almaznetae-my.sharepoint.com/:w:/g/personal/unais_almaz_net_ae/EWIi96BorxtJpSZlQlXTU5gBlrydq...

[unais]

please advise whats the best way to create Day Wise Filter Table with grouping Transaction Type With Date Wise.

[Anonymous]

You can change the granularity of your source data from "document level" to "day & type" level using SUMMARIZE. Refer to the example on your sample data given below...

Start with your Source Data.

Table Name: SourceData

Symbol	Type	Units	Date	Value Per Unit	Total Value
DDD	buy	4	01-01-2020	1.1	4.4
DDD	buy	4	02-01-2020	1.2	4.8
DDD	buy	4	03-01-2020	1.3	5.2
DDD	buy	4	04-01-2020	1.4	5.6
DDD	buy	4	05-01-2020	1.5	6
DDD	sale	13	06-01-2020	2.5	32.5
DDD	buy	4	07-01-2020	1.7	6.8
DDD	buy	4	08-01-2020	1.8	7.2
DDD	buy	4	09-01-2020	1.9	7.6
DDD	buy	4	09-01-2020	1.9	7.6
DDD	sale	8	10-01-2020	3.5	28
DDD	sale	6	11-01-2020	4.5	27
DDD	sale	2	11-01-2020	4.5	9
DDD	sale	1	11-01-2020	4.5	4.5

Step 1: Add a "Calculated Table" to summarize at date and type level (Use NewTable option)

Table1 = 
    SUMMARIZE(
        SourceData,
        SourceData[Symbol],
        SourceData[Type],
        SourceData[Date],
        "Units",SUM(SourceData[Units]),
        "Value",SUM(SourceData[Total Value])
    )

Here we have given "Table1" as the name of the new table that we have created now.

Step 2: Add the following calculated columns. These are the same calculated columns that we have used in my earlier post.

StockInHandToday =
VAR CurrentSymbol = Table1[Symbol]
VAR CurrentDate = Table1[date]
VAR CurrentLineQty = Table1[Units]
VAR TotalPurchases =
    SUMX (
        FILTER ( Table1, Table1[Symbol] = CurrentSymbol && Table1[type] = "buy" ),
        Table1[Units]
    ) + 0
VAR TotalSales =
    SUMX (
        FILTER ( Table1, Table1[Symbol] = CurrentSymbol && Table1[type] = "sale" ),
        Table1[Units]
    ) + 0
VAR PurchasesAfterCurrentDate =
    SUMX (
        FILTER (
            Table1,
            Table1[Symbol] = CurrentSymbol
                && Table1[type] = "buy"
                && Table1[date] > CurrentDate
        ),
        Table1[Units]
    ) + 0
VAR CurrentStock =
    MAX (
        0,
        MIN ( TotalPurchases - TotalSales - PurchasesAfterCurrentDate, CurrentLineQty )
    )
VAR Result =
    IF ( Table1[type] = "Buy", CurrentStock, 0 )
RETURN
    IF ( Result = 0, BLANK (), Result )

StockAgeingInDays = 
VAR CurrentDate = TODAY()
VAR BuyDate = Table1[date]
VAR Age = ROUND(CurrentDate-BuyDate,0)
RETURN Age & " Days"

Add this to a matrix visual and you will get the following result.

In comparison to my earlier solution, the only change we have done this time is that we have changed the granularity of your "SourceData" table from "Document" level to "Day & Type" level using SUMMARIZE columns and used our formula in this new summarized table. To understand more about SUMMARIZE function, refer to the following link...

https://docs.microsoft.com/en-us/dax/summarize-function-dax 

[myesil]

Thank you very much for this great sılution. I've been looking for this kind of solution for months.

[Anonymous]

Additional Info

If you use this method for inventory ageing, although the quantity and age will show correctly, in case if you apply the same to the value of inventory, it will show based on moving average price.

Consider the following "buy" data

Symbol	Type	Units	Date	Value Per Unit	Total Value
DDD	buy	4	09-01-2020	2	8
DDD	buy	4	09-01-2020	3	12
Total		8			20

Here the 8 units purchased on 9th Jan 2020 are at different prices ($2 and $3). So the average value will be $2.5 (20/8). In case you have sold the 4 units bought at $2 and the remaining stocks are of value $3 per unit physically, your ageing report will still show the value based on $2.5 (the moving average) for the remaining 4 units because there is no way the system can determine that the balance stocks are bought at $3.

If you want to address this issue and want to show the value correctly, You are gonna need something like a "batch number" in your data for both "buy" and "sale" transactions and include the same to the "SUMMARIZE" function. If a "batch" number or some similar identifier is available to correlate the "buy" and "sale" data, then accurate value can be shown. Otherwise you will have to be content with the moving average price for "stock-in-hand". Hope you understand this limitation.

[unais]

Hi Sreenath,

Thanks for your support.

I had created Summary Table with Date wise with Transaction type and Material.

But , I had issue with Purchase and Sales Return Transactions these transaction need to treated as seperate

DO No   Date      Material               Type                  QTY                            Reference no

0001       01/01/2019         Aaa        Buy                        15          

0002       02/01/2019         Aaa        Buy                        10          

0003       05/01/2019         Aaa        Purchase return      15                     0001

0004       04/02/2019         Aaa        Sales                        5             

0005       06/02/2019         Aaa        Sales return            2                       0004

Based on Above Scenario how can we handle,

Can we handline adding new QTY column if Return QTY different from original QTY ( Based on Reference No Column Original Doc No )  and if Return QTY is same as original QTY then can Exclude Original and Return transaction  with Additional Column indicator.

[Anonymous · Accepted solution]

I am not very clear what you want to do with Sales Returns and Purchase Returns, but if you want adjust the "Returns" quantity in the original document itself, you can add two calculated columns to your table to get the following results.

DO No	Date	Material	Type	Qty	Reference	Adjustment	AdjQty
0001	01 January 2019	Aaa	Buy	15		15	0
0002	01 February 2019	Aaa	Buy	10			10
0003	01 May 2019	Aaa	Purchase Return	15	0001		15
0004	02 April 2019	Aaa	Sales	5		2	3
0005	02 June 2019	Aaa	Sales Return	2	0004		2

The DAX for the last two columns "Adjustment" & "AdjQty" is as follows...

Adjustment = 
SUMX(
    FILTER(
        zinventory,
        zinventory[Reference]=EARLIER(zinventory[DO No])),
        zinventory[Qty])

AdjQty = zinventory[Qty]-zinventory[Adjustment]

Please note that I have just copied the sample data you sent on your last post and gave the table a name "zinventory". 

Once you do add these columns, instead of running inventory ageing transactions on the "Qty" field, you can run it on "AdjQty" field by changing the formulas accordingly.

If you follow this method, your inventory ageing report will show the ageing based on the original date of purchase and sales.

Alternatively, You can treat all "Purchase Returns" as "Sale" and all "Sales Returns" as "Buy"  and in this approach, you ageing will vary accordingly which will be like a true FIFO stack. The adjustment method mentioned above will be like, you will following "FIFO" only for "Buy" and "Sale" only and violate the FIFO (physically) by inserting at random positions in your stack, if you were to visualise a true FIFO stack operation.

[Anonymous]

Although we have adjusted the "Returns" quantity in the orginal document, the "Returns" line still shows the qty in AdjQty. If you don't want this, you could either make AdjQty to 0 in cases of "Returns" lines using conditional statements or filter out those "Returns" line from the table itself before passing this to the ageing calculation. 

[unais]

hi sreenath.

Actually we need to follow orginal purchase , if we treat sales retun as buy then again will start from Retun Date where has its orginal purchase date may be diffrent...  thanks for your support and appreciated your support.

if you have any sinario for fast moving item DAX query please share me same. Same above data sinario need to Find Fast moving items and High Margin items.