Hello Expertshow can we Sale this item in Fifo Order. once the 1st Purchase is consumed then only it should take from the 2nd Purchasewhat 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.

Hello Allcould anyone pls help me on this

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 RegardsCommunity Support Team _ Polly If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello Anonymous 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

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 RegardsCommunity Support Team _ Polly If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Dax Help to Crack Qty Sold In FIFO Order | Microsoft Fabric Community

11 Replies

learnbcvibe

Frequent Visitor

3 years ago

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

Regular Visitor

2 years ago

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

learnbcvibe
Frequent Visitor
3 years ago
Hello All
could anyone pls help me on this
Anonymous
Not applicable
3 years ago
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
  Frequent Visitor
  3 years ago
  Hello Anonymous
  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
Anonymous
Not applicable
3 years ago
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
  Frequent Visitor
  3 years ago
  Hello Anonymous
  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
Anonymous
Not applicable
3 years ago
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.
Dax Help to Crack Qty Sold In FIFO Order.pbix39 KB
- learnbcvibe
  Frequent Visitor
  3 years ago
  HEllo Anonymous
  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
- learnbcvibe
  Frequent Visitor
  3 years ago
  Hello Anonymous
  
  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
    Frequent Visitor
    3 years ago
    Hello Anonymous
    
    Could you pls look into this.

Forum Discussion

Dax Help to Crack Qty Sold In FIFO Order

11 Replies

Related content

V-Order & Z-Order

Ascending order

Top 5 in descending order

Order switching mid workflow

Date between slicer - Change order