Latest finished product and corresponding quantity

[AdamPowerBI2024]

Hi,

I want to create a last estimate of a produced item (so the latest finished product). I get it correctly with the below Dax formula:

Last Estimate Order =

VAR Last_estimate = MAXX(FILTER(ALLSELECTED(ProductionCalculations), ProductionCalculations[EndedDate.EndedDate]=MAX(ProductionCalculations[EndedDate.EndedDate])), ProductionCalculations[FinishedDate.Finished Date])

RETURN

CALCULATE(MAX(ProductionCalculations[Productions.Prod Id]), FILTER((ProductionCalculations),ProductionCalculations[FinishedDate.Finished Date]= Last_estimate))

I am trying as well to add quantity to the received result. I used the similar formula, but unfortunately it gives me the total sum and it changes when swtiching between orders. Can anybody give a hint how to change that to get the values corresponding to that order?

Last Estimate Qty =

VAR Last_estimate = MAXX(FILTER(ALLSELECTED(ProductionCalculations), ProductionCalculations[EndedDate.EndedDate]=MAX(ProductionCalculations[EndedDate.EndedDate])), ProductionCalculations[FinishedDate.Finished Date])

RETURN

CALCULATE(SUM(ProductionCalculations[Real consump Qty (kg)]), FILTER((ProductionCalculations),ProductionCalculations[FinishedDate.Finished Date]= Last_estimate))

Thank you in advance,

Adam

[AdamPowerBI2024 · Accepted solution]

Hi,

I think I've got it. Thank a lot for your help.

Last Order Quantity in Month (per Product Type) = 

VAR Last_estimate = MAXX(FILTER(ALLSELECTED(ProductionCalculations), ProductionCalculations[EndedDate.EndedDate]=MAX(ProductionCalculations[EndedDate.EndedDate])), ProductionCalculations[FinishedDate.Finished Date])

RETURN

CALCULATE(SUM(ProductionCalculations[Real consump Qty (kg)]),

FILTER((ProductionCalculations),ProductionCalculations[FinishedDate.Finished Date]= Last_estimate

    &&ProductionCalculations[Productions.Prod Id] = CALCULATE(MAX ( ProductionCalculations[Productions.Prod Id] ),

                        FILTER ( ProductionCalculations,ProductionCalculations[FinishedDate.Finished Date] = Last_estimate))))

[AdamPowerBI2024]

Hi @rajendraongole1. thank you for your solution. It works across whole production when looking for the last produced item. But I realized that I need to add 2 filters in that dax formula, 1 for produced item (as we have many) and the 2nd for month-Year. would it be able to add isflitered twice in this formula?

Thank you in advance,

[Anonymous]

Hi @AdamPowerBI2024 ,

Thank very much for @rajendraongole1  solution, I'll add the appropriate conditions based on this for your needs:

Last Estimate Qty =
VAR Last_estimate =
    MAXX (
        FILTER (
            ALLSELECTED ( ProductionCalculations ),
            ProductionCalculations[EndedDate.EndedDate]
                = MAX ( ProductionCalculations[EndedDate.EndedDate] )
        ),
        ProductionCalculations[FinishedDate.Finished Date]
    )
RETURN
    CALCULATE (
        SUM ( ProductionCalculations[Real consump Qty (kg)] ),
        FILTER (
            ProductionCalculations,
            ProductionCalculations[FinishedDate.Finished Date] = Last_estimate
                && ProductionCalculations[Productions.Prod Id]
                    = CALCULATE (
                        MAX ( ProductionCalculations[Productions.Prod Id] ),
                        FILTER (
                            ProductionCalculations,
                            ProductionCalculations[FinishedDate.Finished Date] = Last_estimate
                        )
                    )
                && ProductionCalculations[ProducedItem] = "YourProducedItem"
                && -- Add your produced item filter here
                FORMAT (
                    ProductionCalculations[FinishedDate.Finished Date],
                    "MM-YYYY"
                ) = "YourMonthYear" -- Add your month-year filter here
        )
    )

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[AdamPowerBI2024]

HI, thank you, but still shows blank. While filtering different produced item and year month, I want to see the latest order for each month and for different product with quantity used for that order. I used this one one as proposed:

Last Estimate Qty 2 =

VAR Last_estimate =

    MAXX (

        FILTER (

            ALLSELECTED ( ProductionCalculations ),

            ProductionCalculations[EndedDate.EndedDate]

                = MAX ( ProductionCalculations[EndedDate.EndedDate] )

        ),

        ProductionCalculations[FinishedDate.Finished Date]

    )

RETURN

    CALCULATE (

        SUM ( ProductionCalculations[Real consump Qty (kg)] ),

        FILTER (

            ProductionCalculations,

            ProductionCalculations[FinishedDate.Finished Date] = Last_estimate

                && ProductionCalculations[Productions.Prod Id]

                    = CALCULATE (

                        MAX ( ProductionCalculations[Productions.Prod Id] ),

                        FILTER (

                            ProductionCalculations,

                            ProductionCalculations[FinishedDate.Finished Date] = Last_estimate

                        )

                    )

                && ProductionCalculations[Produced item.ProducedItem] = "YourProducedItem"

                && ProductionCalculations[Produced item.ProducedItem]),

                FORMAT (

                    ProductionCalculations[FinishedDate.Finished Date],

                    "MM-YYYY"

                ) = ProductionCalculations[FinishedDate.Finished YearMth]

        )

Should I also change the formula for the last estimate Order?

Last Estimate Order =

VAR Last_estimate = MAXX(FILTER(ALLSELECTED(ProductionCalculations), ProductionCalculations[EndedDate.EndedDate] = MAX(ProductionCalculations[EndedDate.EndedDate])), ProductionCalculations[FinishedDate.Finished Date])

RETURN

CALCULATE(MAX(ProductionCalculations[Productions.Prod Id]), FILTER((ProductionCalculations),ProductionCalculations[FinishedDate.Finished Date]= Last_estimate))

[AdamPowerBI2024]

Hi,

any possible advice?

Thank you

[rajendraongole1]

Hi @AdamPowerBI2024 -  You can adjust the DAX formula to include the correct filtering for the specific order that matches the Last_estimate. modified one below FYR

Last Estimate Qty =
VAR Last_estimate = MAXX(
FILTER(
ALLSELECTED(ProductionCalculations),
ProductionCalculations[EndedDate.EndedDate] = MAX(ProductionCalculations[EndedDate.EndedDate])
),
ProductionCalculations[FinishedDate.Finished Date]
)
RETURN
CALCULATE(
SUM(ProductionCalculations[Real consump Qty (kg)]),
FILTER(
ProductionCalculations,
ProductionCalculations[FinishedDate.Finished Date] = Last_estimate &&
ProductionCalculations[Productions.Prod Id] =
CALCULATE(MAX(ProductionCalculations[Productions.Prod Id]),
FILTER(ProductionCalculations, ProductionCalculations[FinishedDate.Finished Date] = Last_estimate)
)
)
)

Hope this helps