Compare qty sales with qty stock

[JuanVR11]

Hello Friends!

I´m trying to get the backlog of sales on my area.

I have a table "SALES" with their respective Sales Document. The important fields are:

Pedido - Pos. - Material - Ctd.Ped - Fe.Despacho

It means for this Pedido, Pos, Material the sales must be realized before of 03-03-2020 and with the 60 units.

In other table (MB51), i have the stock movements for a lot of materials with Ped.Clte (same as Pedido), PosPdCl (same as Pos.), Material (same as Material in SALES), Ctd.UME is the qty of movement, and Registrado is the date of the movement.

So, i need to create a column where if the sum of Ctd.UME (MB51) is equal to Ctd.Ped (SALES) and the Registrado date (MB51) is before or equal to Fe.Despacho (SALES) for the same Pedido, Pos, Material.  if this is OK, must be "COMPLETED" if not "INCOMPLETED"

Please sorry for my english and i hope u can help me

Thanks

[v-gizhi-msft · Accepted solution]

Hi,

I have tried both column and measure to reach your requirement:

By column:

Column = 
VAR a =
    CALCULATE (
        SUM ( SALES[Ctd. Ped.] ),
        FILTER (
            ALLSELECTED ( SALES ),
            SALES[Material] = EARLIER ( MB51[Material] )
                && SALES[Pedido] = EARLIER ( MB51[Ped.cite.] )
                && SALES[Pos.] = EARLIER ( MB51[PosPdCI] )
        )
    )
VAR b =
    IF (
        MB51[Ped.cite.] = RELATED ( SALES[Pedido] )
            && MB51[Material] = RELATED ( SALES[Material] )
            && MB51[PosPdCI] = RELATED ( SALES[Pos.] ),
        IF (
            MB51[Registrado] <= RELATED ( SALES[Fe. Despacho] ),
            CALCULATE (
                SUM ( MB51[Ctd.UME] ),
                FILTER (
                    ALLSELECTED ( MB51 ),
                    MB51[Ped.cite.] = EARLIER ( MB51[Ped.cite.] )
                        && MB51[Material] = EARLIER ( MB51[Material] )
                        && MB51[PosPdCI] = EARLIER ( MB51[PosPdCI] )
                )
            ),
            -1
        ),
        -1
    )
RETURN
    IF ( a = b, "COMPLETED", "INCOMPLETED" )

By measure:

Measure = 
VAR a =
    CALCULATE (
        SUM ( SALES[Ctd. Ped.] ),
        FILTER (
            ALLSELECTED ( SALES ),
            SALES[Material] IN FILTERS ( MB51[Material] )
                && SALES[Pedido] IN FILTERS ( MB51[Ped.cite.] )
                && SALES[Pos.] IN FILTERS ( MB51[PosPdCI] )
        )
    )
VAR b =
    IF (
        MAX ( MB51[Ped.cite.] ) IN FILTERS ( SALES[Pedido] )
            && MAX ( MB51[Material] ) IN FILTERS ( SALES[Material] )
            && MAX ( MB51[PosPdCI] ) IN FILTERS ( SALES[Pos.] ),
        IF (
            MAX ( MB51[Registrado] ) <= MAX ( SALES[Fe. Despacho] ),
            CALCULATE (
                SUM ( MB51[Ctd.UME] ),
                FILTER (
                    ALLSELECTED ( MB51 ),
                    MB51[Ped.cite.] IN FILTERS ( MB51[Ped.cite.] )
                        && MB51[Material] IN FILTERS ( MB51[Material] )
                        && MB51[PosPdCI] IN FILTERS ( MB51[PosPdCI] )
                )
            ),
            -1
        ),
        -1
    )
RETURN
    IF ( a = b, "COMPLETED", "INCOMPLETED" )

The result in my sample shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto