Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
When I don't have the Pur_part in the other warehouses the DAX consolidates qty on-hand stock for all the warehouse.
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.
Not sure why this happens but when I sort the DAX by descending, sometimes it provides the right qty and others some funny results.
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.
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |