Need help with the DAX Calculation

[Erick24]

Hello,

I hope all is good.

I'm working with a system it works like this:

I have "On hand" material that is going to be delivered to orders, but when it runs out it has to be supplied by means of a PO or a DO (status 25/24) and I have to assign the PO or the DO to the order that is going to supply it as shown in the following table.

The PO can have several lines for the same material, so an order can be delivered with line 1 of a PO and another order with line 2 of the same PO depending on the quantity of pieces to be supplied.

In the proj ohb column, the material that is missing to be supplied or the one that is available when there are positive numbers is accumulated.

Note:

The rows are order by the row number column that is by date and part number

You can download the file in the following link:

Test Microsoft Fabric.xlsx

The page named "Database Example" can you used to do the example.

Regards.

[Erick24 · Accepted solution]

Hello, thank you for the support.

Yes, is necessary because with that information I can see the PO/DO will have the material to the order.

It works but some details are missing. Could you help me? I'll tell you what I need.

For example, for product number "SR125", if I change DO to PO, I only detect the first PO, not the second.

The result should be like this:

Other example with the following part number "SR124", the result sould be:

Please see the project pbix in the following link:

Test Project.pbix

Let me know if you need something else.

Thanks!

[Anonymous]

Hi @Erick24 ,

Please create an index column first and try the code below.

Proj oha(accumulated) = 
VAR CurrentOrder = 'Simple'[Need]
VAR MaterialOnHand = 'Simple'[Have]
VAR PartNumber = 'Simple'[Part Number]
VAR OrderDate = 'Simple'[Index]

VAR PreviousOrders = 
    FILTER(
        'Simple',
        'Simple'[Part Number] = PartNumber &&
        'Simple'[Index] <= OrderDate &&
        'Simple'[Type] = "Order"
    )

VAR PreviousPOs = 
    FILTER(
        'Simple',
        'Simple'[Part Number] = PartNumber &&
        'Simple'[Index] <= OrderDate &&
        'Simple'[Type] = "PO"
    )

VAR PreviousDOs = 
    FILTER(
        'Simple',
        'Simple'[Part Number] = PartNumber &&
        'Simple'[Index] <= OrderDate &&
        'Simple'[Type] = "DO"
    )

VAR TotalPreviousOrders = SUMX(PreviousOrders, 'Simple'[Need])
VAR TotalPreviousPOs = SUMX(PreviousPOs, 'Simple'[Need])
VAR TotalPreviousDOs = SUMX(PreviousDOs, 'Simple'[Need])

VAR AvailableStock = MaterialOnHand + TotalPreviousPOs + TotalPreviousDOs

RETURN
AvailableStock + TotalPreviousOrders

Uses some of the data from your table for a more visual presentation.

If you have any other concerns, please feel free to share with us here so that we can offer more support.!

Best regards,

Lucy Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Erick24]

Hello thank you,

I have the calculated column by proj ohb (accumulated).

The result I'm looking for are the columns "What is to be supplied?", "Number" and "Line columns". Could you please help? If you need something else please say me

[Anonymous]

Hi @Erick24 ,

Thank you very much for your kind reply and I apologise for misinterpreting your needs in my last reply!

Is it necessary to achieve the following effects? 

I created a new sum column to calculate the total amount of materials consumed for different types of goods. 

Sum of Transaction quantity = 
VAR CurrentIndex = 'Table'[Index]
RETURN
     IF(
        'Table'[Type] = "Order" && 'Table'[Part Number] = "SR125",
        CALCULATE(
            SUMX(
                FILTER(
                    ALL('Table'),
                    'Table'[Index] <= CurrentIndex && 'Table'[Type] = "Order" && 'Table'[Part Number] = "SR125"
                ),
                ABS('Table'[Transaction quantity])
            )
        ),
        IF('Table'[Type] = "Order" && 'Table'[Part Number] = "SR352",
        CALCULATE(
            SUMX(
                FILTER(
                    ALL('Table'),
                    'Table'[Index] <= CurrentIndex && 'Table'[Type] = "Order" && 'Table'[Part Number] = "SR352"
                ),
                ABS('Table'[Transaction quantity])
            )
        ),
        BLANK()
    ))

This column is used to differentiate between supplying from PO or DO.

WhatWillBeToSupply = 
VAR SR125 = 30
VAR SR352 = 10
VAR POQuantity =
CALCULATE(
    MAX('Table'[Transaction quantity]),
    FILTER(
        'Table',
        'Table'[Part Number] = "SR125" && 'Table'[Type] = "PO")
)

VAR DOQuantity =
CALCULATE(
    MAX('Table'[Transaction quantity]),
    FILTER(
        'Table',
        'Table'[Part Number] = "SR125" && 'Table'[Type] = "DO")
)

VAR PreviousPO = SR125 + POQuantity
VAR PreviousDO = SR125 + POQuantity +DOQuantity
RETURN
SWITCH(
    TRUE(),
    'Table'[Sum of Transaction quantity] <= SR125 && 'Table'[Part Number] = "SR125" && 'Table'[Type] = "Order", "In Stock",
    'Table'[Sum of Transaction quantity] <= PreviousPO && 'Table'[Part Number] = "SR125" && 'Table'[Type] = "Order", "PO",
    'Table'[Sum of Transaction quantity] <= PreviousDO && 'Table'[Part Number] = "SR125" && 'Table'[Type] = "Order", "DO",
    'Table'[Sum of Transaction quantity] <= SR352 && 'Table'[Part Number] = "SR352" && 'Table'[Type] = "Order", "In Stock",
    BLANK()
)

You could get more detailed information by checking the attachment.

If you have any other concerns, please feel free to share with us here so that we can offer more support.!

Best regards,

Lucy Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Erick24 · Accepted solution]

Hello, thank you for the support.

Yes, is necessary because with that information I can see the PO/DO will have the material to the order.

It works but some details are missing. Could you help me? I'll tell you what I need.

For example, for product number "SR125", if I change DO to PO, I only detect the first PO, not the second.

The result should be like this:

Other example with the following part number "SR124", the result sould be:

Please see the project pbix in the following link:

Test Project.pbix

Let me know if you need something else.

Thanks!

[Anonymous · Accepted solution]

Hi @Erick24 ,

Thank you for your kind reply.

Here are the final results returned for SR125 and SR124, please check to see if they meet your requirements.

This occurs because of a missing unique index key. In the original code, we determine their respective values via DO or PO.

If you change DO to PO, we need to re-add a new unique index to the filter criteria. 

Based on the data in the table you provided, you can add filters in several ways.

You could get more detailed information by checking the attachment.

If you have any other concerns, please feel free to share with us here so that we can offer more support.!

Best regards,

Lucy Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Erick24]

Hello Ibendlin, thank you for the information.

I have added a link where you can see the file.

Thank you again.

[lbendlin]

Why do you always have 30 on hand? shouldn't line 2 be a mix of in stock and PO?

[Erick24]

This is how the system works, a field with the material on hand and the material for the orders is subtracted and then added with the PO and DO as in the example but I only have 30 pieces for the first part number, and 10 pieces for the second part number.

[lbendlin]

shouldn't line 2 be a mix of in stock and PO?

[Erick24]

For the first part number I have 30 pieces on hand.
For the second line i need supply 10 pieces, which I have on hand (in stock). I supply them and I have 20 pieces left.
For the third line i need supply 50 pieces, but I only have 20 pieces, so I owe 30 pieces and these 30 pieces will be supplied with PO 21275 Line 1 (the PO can have several lines) until 2/5/2025, which is when the PO arrives. Since that PO receives 140 pieces, I can supply the other lines until it runs out of material and the material from the DO arrives to supply the remaining lines.

Did I answer your question?