Forum Discussion

Erick24

Frequent Visitor

1 year ago

Solved

Need help with the DAX Calculation

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.

Need Help

Erick24
1 year ago
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
1 year ago
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.
Test Project-add.pbix61 KB

11 Replies

lbendlin
Super User
1 year ago
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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
- Erick24
  Frequent Visitor
  1 year ago
  Hello Ibendlin, thank you for the information.
  I have added a link where you can see the file.
  Thank you again.
  - lbendlin
    Super User
    1 year ago
    Why do you always have 30 on hand? shouldn't line 2 be a mix of in stock and PO?

Anonymous

Not applicable

1 year ago

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.

Need help with the DAX Calculation.pbix76 KB

Erick24

Frequent Visitor

1 year ago

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

Not applicable

1 year ago

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.

Need help with the DAX Calculation.pbix87 KB

Forum Discussion

Need help with the DAX Calculation

11 Replies

Related content

MoM calculation

Calculation Help

Calculate Tooltips

Hour Calculation

Calculating with Filters