Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
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
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
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.
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
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.
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
Hello All
could anyone pls help me on this
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |