Forum Discussion
Helper ICompare SALES qty by position/document vs STOCK table
Hi Friends.
I´m looking for a DAX formula that compares two tables, Sales and Stock. In Sales i have the sales by Document, Position, Material and Qty. In the Stock i have the movements for the respective Document, position and material. I need to compare de sum qty of stock for a Pedido/Pos/Material with the respective Pedido/Pos/Material in Sales table. and if the sum of qty in stock is >= to the Sales table put a "1" in a column in SALES, if is less then put "0" in a column in SALES, to finally get the average of compliance by Pedido/pos/Material.
Sorry for my english 😛
I hope u can help me.
Thanks
Hi JuanVR11 ,
At first, you need to create a relationship based on "Material" column between two tables. Then create a column with following DAX.
Column = VAR a = CALCULATE ( SUM ( 'Stock'[QTY] ), ALLEXCEPT ( 'Sales', 'Sales'[Material], 'Sales'[Pedido], 'Sales'[Pos] ) ) RETURN IF ( a < 'Sales'[QTY], 0, 1 )Here is my test file for your reference.
Hi JuanVR11 ,
You could refer to the following DAX, I added a date condition in it.
Column = VAR a = CALCULATE ( SUM ( 'Stock'[QTY] ), ALLEXCEPT ( 'Sales', 'Sales'[Material], 'Sales'[Pedido], 'Sales'[Pos] ) ) VAR b = CALCULATE ( MAX ( Stock[Mvt Date] ), ALLEXCEPT ( 'Sales', 'Sales'[Material], 'Sales'[Pedido], 'Sales'[Pos] ) ) RETURN IF ( a < 'Sales'[QTY] || b > Sales[Delivery Date], 0, 1 )
5 Replies
- Greg_Deckler
Community Champion
JuanVR11 - Not sure I understand the issue, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2. - v-eachen-msft
Community Support
Hi JuanVR11 ,
At first, you need to create a relationship based on "Material" column between two tables. Then create a column with following DAX.
Column = VAR a = CALCULATE ( SUM ( 'Stock'[QTY] ), ALLEXCEPT ( 'Sales', 'Sales'[Material], 'Sales'[Pedido], 'Sales'[Pos] ) ) RETURN IF ( a < 'Sales'[QTY], 0, 1 )Here is my test file for your reference.
JuanVR11Thanks for your reply my friend.
I tried the PBI file you sent, it works in some cases, but in others don´t.
as you can see in the image, the file "Material: 40330098145", "Pedido: 1010024533", "Pos: 20" must be "1" because the sum of STOCK for the same key is 4 as the respective key in SALES table.
Also it is important to mention that Material in STOCK table may be more than one, as the Material in SALES table.
Here is the pbi file with a real scenario. I hope u can help me with it.
https://1drv.ms/u/s!AmfHhSleU-H8jWQTzXyeVUj2f-vf?e=5ejeWM
Thanks a lot.
- JuanVR11
Hello my friend.
Now it works, apparently i had an issue with some material codes that makes an error in the dax formula.
I have a last question. If in the SALES table i have a "Delivery Date" and every unit in table STOCK have their respective Mvt Date, how can in a dax formula says that only if the last unit date is before the "Delivery Date" in SALES must be "1" if don´t "0"???
Here´s an example
i Hope u can help me.
Thanks a lot.
- v-eachen-msft
Hi JuanVR11 ,
You could refer to the following DAX, I added a date condition in it.
Column = VAR a = CALCULATE ( SUM ( 'Stock'[QTY] ), ALLEXCEPT ( 'Sales', 'Sales'[Material], 'Sales'[Pedido], 'Sales'[Pos] ) ) VAR b = CALCULATE ( MAX ( Stock[Mvt Date] ), ALLEXCEPT ( 'Sales', 'Sales'[Material], 'Sales'[Pedido], 'Sales'[Pos] ) ) RETURN IF ( a < 'Sales'[QTY] || b > Sales[Delivery Date], 0, 1 )
