Microsoft Power BI Data Analyst Practice Question

The correct DAX formula is CALCULATE(SUM('Inventory'[UnitsInStock]), LASTDATE('Date'[Date])). Here's why:

• Correct Answer: This formula uses the CALCULATE function to modify the filter context. The LASTDATE('Date'[Date]) function returns a table containing only the last date in the current filter context (e.g., the last day of a selected month). CALCULATE then applies this new single-day filter context to the SUM('Inventory'[UnitsInStock]) expression, effectively calculating the stock for only the last day of the period. This is the standard pattern for handling semi-additive measures like inventory balances.

• SUM('Inventory'[UnitsInStock]): This is incorrect because it is a fully additive measure. It would sum the 'UnitsInStock' for every day in the selected period, leading to a vastly inflated and incorrect number. For example, the inventory for a month would be the sum of 30 daily balances instead of the balance on the last day.

• AVERAGEX(VALUES('Date'[Date]), SUM('Inventory'[UnitsInStock])): This is incorrect because it calculates the average daily stock over the period, not the closing balance on the last day. While this might be a useful metric in some scenarios, it does not meet the requirement for a closing balance.

• TOTALYTD(SUM('Inventory'[UnitsInStock]), 'Date'[Date]): This is incorrect as it is a time intelligence function that calculates the cumulative (year-to-date) total. It does not isolate the value from the last day of the period; instead, it sums all values from the start of the year up to the end of the current period.