Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
raphael_cabral
Frequent Visitor

On Hand Qty After netting off SO qty.

 

 

 

 

 

3 REPLIES 3
v-xicai
Community Support
Community Support

Hi @raphael_cabral ,

 

>>if there any way I can calculate the On hand Stock after netting of Backlog qty per SO line/part?

 

From your description, the "per SO line/part" is referred to per 'BL Availability'[pur_part] ? If so, you may follow steps below.  If not, you may change the red part in formula below according to your requirement.

 

firstly, you may change the Cross filter direction of relationships table 'SO Dates' and 'BL Availability' from Single to Both , which will take these tables treated as a single table.

 

Then you may create measure like DAX below.

 

Net On hand Stock=

Var filter1= FILTER(ALLSELECTED('BL Availability'), 'BL Availability'[pur_part] =MAX('BL Availability'[pur_part]))

Var filter2= FILTER(ALLSELECTED('SO Dates'), YEAR('SO Dates'[Released_Date]) =YEAR(MAX('SO Dates'[Released_Date]))&& 'SO Dates'[SPGI_Quarter] =MAX('SO Dates'[SPGI_Quarter]) )

Var SumOnhand=CALCULATE(SUM(Stock[On_Hand_Qty]),filter1, filter2 )

Var SumBacklog=CALCULATE(SUM('BL Availability'[backlog_qty]),filter1, filter2 )

Return

SumOnhand- SumBacklog

 

Best Regards,

Amy 

 

Community Support Team _ Amy

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

Thank you Amy for your reply, I think we are in the right direction as that the DAX you created sorted part of my problem. 🙂

 

Answer your question, "per SO line/part" is referred to per 'BL Availability'[pur_part] , yes! my Pur_part is the equivalent of SO line/Part.

 

However, I got an error when I don't add the second sentence in red on the first  VAR,  "filter1= FILTER(ALLSELECTED('BL Availability')" so I had to add the red part to make DAX works. Please advise.

 

error.PNG

 

When I don't have the Pur_part in the other warehouses the DAX consolidates qty on-hand stock for all the warehouse.

Capture1.PNG

When I have a part on Pur_Part and it is not showing in stock, the DAX consolidates all the parts which are in stock but not in the Pur_Part list.

 

Capture2.PNG

 

Not sure why this happens but when I sort the DAX by descending, sometimes it provides the right qty and others some funny results.

 

Capture3.PNG

 

again thank you very much Amy for your help and I hope you can help make the enhancements to get the DAX working properly. 

 

Hi @raphael_cabral ,

 

You may try to create measure like DAX below.

 

Net On hand Stock1=

Var filter1= FILTER( 'BL Availability' ,  'BL Availability'[pur_part] =MAX('BL Availability'[pur_part]) &&'BL Availability'[Warehouse]=MAX('BL Availability'[Warehouse]) )

Var filter2= FILTER( 'SO Dates', YEAR('SO Dates'[Released_Date]) =YEAR(MAX('SO Dates'[Released_Date]))&& 'SO Dates'[SPGI_Quarter] =MAX('SO Dates'[SPGI_Quarter]) )

Var SumOnhand=CALCULATE(SUM(Stock[On_Hand_Qty]),filter1, filter2 )

Var SumBacklog=CALCULATE(SUM('BL Availability'[backlog_qty]),filter1, filter2 )

Return

SumOnhand- SumBacklog



Or



Net On hand Stock2= SUM(Stock[On_Hand_Qty])- SUM('BL Availability'[backlog_qty])

 

Best Regards,

Amy 

 

Community Support Team _ Amy

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.