Last produced order in a month with a corresponding qty of this order

[AdamPowerBI2024]

Hi,

I created a column that shows the last order of each product type and for each month. I am however unable to get the corresponding quantity of that order.

For the Order I am using this formula:

Last Order in a month (per produced type) =

CALCULATE(MAXX(SUMMARIZE(ProductionCalculations,ProductionCalculations[FinishedDate.Finished YearMth],

ProductionCalculations[Prod.Item&Prod.Name],"count",

MAX(ProductionCalculations[Productions.Prod Id])),[count]),

ALLEXCEPT(ProductionCalculations,ProductionCalculations[FinishedDate.Finished YearMth],

ProductionCalculations[Prod.Item&Prod.Name]))

How to change the formula to get the corresponding quantity. I try but I always get a sum.

Thank you in advance,

Adam

[AdamPowerBI2024]

Hi,

Thank you for your help but I think i don't fully understand your logic. I wanted to avoid creating new tables. 

Maybe is possbile to rewrite the forrula that I use for the most recent order in a month as it works well. Can we based on this formula, createa formula that pulls out the quantity referring to that order?

CALCULATE(MAXX(SUMMARIZE(ProductionCalculations,ProductionCalculations[FinishedDate.Finished YearMth],

ProductionCalculations[Prod.Item&Prod.Name],"count",

MAX(ProductionCalculations[Productions.Prod Id])),[count]),

ALLEXCEPT(ProductionCalculations,ProductionCalculations[FinishedDate.Finished YearMth],

ProductionCalculations[Prod.Item&Prod.Name]))

[AdamPowerBI2024]

Unfortunately, Earlier has an error

[rajendraongole1]

Hi @AdamPowerBI2024 -restructed the logic , can you try below

Last Order Quantity in Month (per Product Type) =
VAR LastOrderDate =
CALCULATE(
MAX(ProductionCalculations[FinishedDate.Finished YearMth]),
ALLEXCEPT(
ProductionCalculations,
ProductionCalculations[Prod.Item&Prod.Name]
)
)
VAR LastOrder =
CALCULATE(
MAX(ProductionCalculations[Productions.Prod Id]),
FILTER(
ProductionCalculations,
ProductionCalculations[FinishedDate.Finished YearMth] = LastOrderDate &&
ProductionCalculations[Prod.Item&Prod.Name] = MAX(ProductionCalculations[Prod.Item&Prod.Name])
)
)
RETURN
CALCULATE(
SUM(ProductionCalculations[Quantity]), -- Assuming you have a Quantity column
FILTER(
ProductionCalculations,
ProductionCalculations[FinishedDate.Finished YearMth] = LastOrderDate &&
ProductionCalculations[Productions.Prod Id] = LastOrder
)
)

[AdamPowerBI2024]

HI,

This one doesn't work. The below formula that iI used works for both month and produced item. The one you proposing is pulling the quantity of the last produced order . I can chage between different types of produced items but cannot change the month filter. 

Last Order in a month (per produced type) =

CALCULATE(MAXX(SUMMARIZE(ProductionCalculations,ProductionCalculations[FinishedDate.Finished YearMth],

ProductionCalculations[Prod.Item&Prod.Name],"count",

MAX(ProductionCalculations[Productions.Prod Id])),[count]),

ALLEXCEPT(ProductionCalculations,ProductionCalculations[FinishedDate.Finished YearMth],

ProductionCalculations[Prod.Item&Prod.Name]))

I would like to switch between months and check the quantity for the last produced item in a given month.

[Anonymous]

Thanks for the reply from rajendraongole1 , please allow me to provide another insight:

Hi, @AdamPowerBI2024 

From your provided DAX, I understand that you are determining whether a date is the largest for the month based on the size of the ID. Based on this, I have devised the following solution, which I hope you find helpful:

1.Here is my sample data:

2.I have created the following calculated table:

Table = 
VAR Latestproduced =
    SUMMARIZE (
        'ProductionCalculations',
        'ProductionCalculations'[FinishedDate.Finished YearMth],
        'ProductionCalculations'[Prod.Item&Prod.Name],
        "Latest id", MAX ( 'ProductionCalculations'[Productions.Prod Id] ),
        "Q",
            VAR LI =
                MAX ( 'ProductionCalculations'[Productions.Prod Id] )
            RETURN
                CALCULATE (
                    SUM ( 'ProductionCalculations'[Quantity] ),
                    'ProductionCalculations'[Productions.Prod Id] = LI
                )
    )
RETURN
    Latestproduced

3.Below are the final results, which I hope will assist you:

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[AdamPowerBI2024]

Hi,

something doesn't work. Should I format something?

[Anonymous]

Hi, @AdamPowerBI2024 

Thank you for your prompt reply.

In my example, I created a calculated table, whereas I suspect you might be using a measure:

For further details, please refer to:

Using calculated tables in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,

Leroy Lu

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

[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))))

[Anonymous]

Hi, @AdamPowerBI2024 
We are delighted that you have found a solution and are willing to share it.

Accepting your post as the solution is incredibly helpful to our community, as it enables members with similar issues to find answers more quickly.

Thank you for your valuable contribution to the community, and we wish you all the best in your work.

Best Regards,

Leroy Lu

[rajendraongole1]

Hi @AdamPowerBI2024 - you can write a measure that captures the last order's quantity as per shared measure, please modify as below:

Last Order Quantity in Month (per Product Type) =
VAR LastOrderDate =
CALCULATE(
MAX(ProductionCalculations[FinishedDate.Finished YearMth]),
ALLEXCEPT(
ProductionCalculations,
ProductionCalculations[Prod.Item&Prod.Name]
)
)

VAR LastOrder =
CALCULATE(
MAX(ProductionCalculations[Productions.Prod Id]),
FILTER(
ProductionCalculations,
ProductionCalculations[FinishedDate.Finished YearMth] = LastOrderDate &&
ProductionCalculations[Prod.Item&Prod.Name] = EARLIER(ProductionCalculations[Prod.Item&Prod.Name])
)
)

RETURN
CALCULATE(
SUM(ProductionCalculations[Quantity]), -- Assuming you have a Quantity column
FILTER(
ProductionCalculations,
ProductionCalculations[FinishedDate.Finished YearMth] = LastOrderDate &&
ProductionCalculations[Productions.Prod Id] = LastOrder
)
)

I hope this works in your case.