Compare SALES qty by position/document vs STOCK table

[JuanVR11]

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

[v-eachen-msft · Accepted solution]

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.

[v-eachen-msft · Accepted solution]

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.

[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 · Accepted solution]

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 )

[JuanVR11]

Thanks 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.

[Greg_Deckler]

@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.