BenchMark Quizzes by MDFT Pro

Calculate Inventory Level On Last Production Day Each Month

You plan to create a visual at MDFT Pro that shows inventory levels for parts and finished products on the last production day of each month. The academy needs to track inventory accurately for reporting and planning purposes.

You have the following DAX expression:

Last Inventory Level :=
CALCULATE(
    SUM(ProductInventory[Inventory Count]),
    FILTER(
        ALL(ProductInventory[Date]),
        ProductInventory[Date] = ____________
    )
)

How should you complete the DAX expression?

Choose the correct answer from the options below.

Explanations for each answer:

LASTDATE(ProductInventory[Date]) is incorrect. LASTDATE retrieves the last date in the current filter context, which may not be the last production day if there is no data for that day. This can result in missing the actual last day of production, especially if the last calendar day is a weekend or holiday with no inventory activity.
LASTNONBLANK(ProductInventory[Date], SUM(ProductInventory[Inventory Count])) is correct. LASTNONBLANK gets the latest date in the period for which there is data, ensuring the last production day is used.
CLOSINGBALANCEMONTH(ProductInventory[Inventory Count], ProductInventory[Date]) is incorrect. CLOSINGBALANCEMONTH returns the balance amount (a numeric value) at the end of the month, but only if there is data on the last calendar day. Using it in this DAX expression would result in a syntax error because the comparison requires a date value, not a numeric result.
MAX(ProductInventory[Date]) is incorrect. MAX returns the maximum date in the current filter context, not necessarily the last production day. If there is no data on the last calendar day, it may return an earlier date or BLANK, so it does not reliably provide the inventory level on the last day of production.

Learn more about LASTNONBLANK in Power BI:

LASTNONBLANK Function

Exam Preparation Quiz

Calculate Inventory Level On Last Production Day Each Month