Forum Discussion

learnbcvibe's avatar
learnbcvibe
Frequent Visitor
3 years ago

Dax Help to Crack Qty Sold In FIFO Order

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 w...
Need Help
Tips and Tricks
Anonymous's avatar
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.

learnbcvibe's avatar
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's avatar
    learnbcvibe
    Frequent Visitor
    3 years ago

    Hello Anonymous 

     

    Could you pls look into this.