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
JuanVR11
Helper I
Helper I

Compare qty sales with qty stock

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.

Pedidos.jpg

 

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.

 

MB51.jpg

 

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

1 ACCEPTED SOLUTION
v-gizhi-msft
Community Support
Community Support

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:

21.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

 

View solution in original post

1 REPLY 1
v-gizhi-msft
Community Support
Community Support

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:

21.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

 

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.