Dax Help to Crack Qty Sold In FIFO Order

[learnbcvibe]

Hello Experts

how can we Sale this item in Fifo Order. once the 1st Purchase is consumed then only it should take from the 2nd Purchase

what is the way to acheive it.

In this Eg: once item A has with 500 Qty. till that 500 qty was consumed it should take from the 2nd Purchase of 300 qty.

[v-rongtiep-msft]

Hi @learnbcvibe ,

Please refer to my pbix file.

Add an index column.

Then create measure.

Measure = var _1=CALCULATE(SUM('Table'[Qty]),FILTER(ALL('Table'),'Table'[Type]="Purchase"&&'Table'[Item]=SELECTEDVALUE('Table'[Item])&&'Table'[Index]<=SELECTEDVALUE('Table'[Index])))
var _sales=CALCULATE(SUM('Table'[Qty]),FILTER(ALL('Table'),'Table'[Type]="Sale"&&'Table'[Item]=SELECTEDVALUE('Table'[Item])&&'Table'[Index]<=SELECTEDVALUE('Table'[Index])))
return _sales

purchase = CALCULATE(SUM('Table'[Qty]),FILTER(ALL('Table'),'Table'[Type]="Purchase"&&'Table'[Item]=SELECTEDVALUE('Table'[Item])&&'Table'[Index]<=SELECTEDVALUE('Table'[Index])))

Measure 2 = var _1= MAXX(FILTER(ALL('Table'),'Table'[Item]=SELECTEDVALUE('Table'[Item])),[Measure])
var _1re=
[purchase]-_1
return
IF(_1re<=0,_1re,BLANK())

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[learnbcvibe]

Hello @v-rongtiep-msft 

Cost will weighteed average cost, Total item cost/ Total Qty

Item sale should be in fifo Order
here the issue is we cant add index column why because the data soruce is from cloud. so there may be issue in refresh (i think so)
we have entry type column is there to differenciate sales and purhcase and Posting date is there 

Sample Data and Output

[learnbcvibe]

Hello @v-rongtiep-msft 

Could you pls look into this.

[learnbcvibe]

HEllo @v-rongtiep-msft 

Thanks for your effort

first time am getting this much of help , i really apprcciate your effort from bottom of my heart

here the issue is we cant add index column why because the data soruce is from cloud. so there may be issue in refresh (i think so)
we have entry type column is there to differenciate sales and purhcase and Posting date is there 

[v-rongtiep-msft]

Hi@ @learnbcvibe ,

Please have atry.

Create a measure.

measure =
VAR _1 =
    CALCULATE (
        SUM ( table[qty] ),
        FILTER ( ALL ( table ), table[item] = SELECTEDVALUE ( table[item] ) )
    )
RETURN
    //the value you purchase
    MAX ( table[value] ) - _1

If it still does not help, please provide more details with the desired output(It is better to express the output you want in a table form, just  like  the data you gave.

How to Get Your Question Answered Quickly 

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[learnbcvibe]

Hello @v-rongtiep-msft 

Thankyou So much for the effort,

am working on this for making Item aging Report. so the item should sold based on Fifo Order.

but last 10 days i cant able to this.lot of videos,articles watched. as a beginner its very hard to get it

could you pls help me on this.  Sale should go in fifo order. if the stock is available based or purchase, we need to take the aging

Thankyou

[v-rongtiep-msft]

Hi @learnbcvibe ,

I have seen your data, but according to what you said the first 500 was bought, the next one starts from 300. How much do you have to buy at once? Judging by what? And is it related to the type of [Type] in the table?

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[learnbcvibe]

Hello @v-rongtiep-msft 

Thankyou for the reply.

Here type column is to filter for Sale and Purcahse. 

for Eg: if any Sales Comes for Item A, it should only take from the first purchase of Qty 500 until that qty has finished(Fifo Qty Order). once the 500 QTy of item A finished then only it start using from the Purcahse of 300 QTY

[learnbcvibe]

Hello All

could anyone pls help me on this

[learnbcvibe]

i asked this question after googling alot and checked the forum also. i cant able to get any response match with this query.

kindly request all BI Experts pls help to Solve this issue

[myesil]

This post solved similar question
Solved: Inventory Qutantity wise Againg Report FIFO - Microsoft Fabric Community

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