Microsoft Certified Power Bi Data Analyst Associate · Free Practice Question Medium
Question 24
At EcoMetrics Corp., your inventory report includes the Inventory table with the following columns:
DateProductIDStockQuantity
Your manager asks you to create a measure that shows the closing stock quantity for the last date in the selected time period.
Which DAX formula should you use?
-
A
Closing Stock = SUM(Inventory[StockQuantity])
-
B
Closing Stock = CALCULATE(LASTNONBLANK(Inventory[StockQuantity], 1), LASTDATE(Inventory[Date]))
-
C
Closing Stock = CALCULATE(SUM(Inventory[StockQuantity]), LASTDATE(Inventory[Date]))
-
D
Closing Stock = LASTNONBLANK(Inventory[StockQuantity], Inventory[Date])
Reveal correct answer
Correct answer: C
Explanation
Solution with CALCULATE and LASTDATE
At EcoMetrics Corp., your Inventory table includes Date, ProductID, and StockQuantity, and your manager wants a measure for the closing stock quantity on the last date of a selected time period. In Power BI Desktop, select the Inventory table in the "Modeling" tab, click "New Measure," and enter:
Closing Stock = CALCULATE(SUM(Inventory[StockQuantity]), LASTDATE(Inventory[Date]))
Press Enter to create the measure. This sums StockQuantity for the latest date in the current filter context (e.g., if filtered to January 2024, it uses January 31, 2024). For example, with data (Jan 30, P1, 50; Jan 31, P1, 60; Jan 31, P2, 40), LASTDATE selects Jan 31, and SUM totals 60 + 40 = 100 as the closing stock.
Why This Works
LASTDATE(Inventory[Date]) identifies the most recent date in the filter context (e.g., from a slicer or page filter), and CALCULATE applies this filter to SUM(Inventory[StockQuantity]), aggregating stock for that date across all ProductIDs. This ensures the measure dynamically reflects the last day’s total stock in any selected period (e.g., month, quarter), meeting the requirement. The Microsoft documentation on LASTDATE states, “LASTDATE returns the latest date in a column within the current context, perfect for end-of-period measures like closing stock.”
Why Not Other Options?
SUM(Inventory[StockQuantity]): This sums StockQuantity across all dates in the context, not just the last date, giving a total (e.g., 50 + 60 + 40 = 150), not a closing value.
CALCULATE(LASTNONBLANK(Inventory[StockQuantity], 1), LASTDATE(Inventory[Date])): LASTNONBLANK finds the last non-blank value in a column ordered by another (here, a constant 1), not summing across rows—it’s for single values, not totals, per LASTNONBLANK documentation.
LASTNONBLANK(Inventory[StockQuantity], Inventory[Date]): This returns the last non-blank StockQuantity ordered by Date for one row, not a sum (e.g., 60 or 40, not 100), missing the total requirement.
CALCULATE with SUM and LASTDATE delivers the precise closing stock total for EcoMetrics.
A.
This formula sums up all stock quantities in the Inventory table, regardless of date. It does not filter for the last date in the selected time period. It simply returns the total stock.
B.
This expression misuses LASTNONBLANK with a constant 1 as the second argument, which is invalid DAX logic. The formula does not properly filter StockQuantity by the last date in context. LASTNONBLANK should be used with a valid expression, not a constant.
C.
LASTDATE(Inventory[Date]) returns the last visible date in the current context.
CALCULATE applies that filter to the measure, ensuring the result only includes values for that date.
It dynamically reflects the closing stock on the latest date selected by filters, slicers, or visuals.
D.
This formula misuses the arguments for LASTNONBLANK, where the second argument must be an expression evaluated for non-blank values (usually a measure or column expression, not just a date column). It won't accurately return closing stock for the last date in context and might behave inconsistently depending on the data.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
