This is a really complex problem, unless I'm missing something. Using LASTNONBLANK()
will only get you part of the way to the solution.
I have never yet seen anyone post a method to address your situation, though I have seen a few that are similar in some ways
Matt Allington: https://exceleratorbi.com.au/lastnonblank-explained/ shows how to get the last value to show correctly in the subtotal area when there is missing data at the end of the column.
Marco Russo: https://www.sqlbi.com/articles/semi-additive-measures-in-dax/ is instructive but it carries the final balance on until you run out of dates in the date table.
I'm not saying that this measure will be the most efficient, but it should achieve what you seek:
Most Recent Balance =
-- The first day of the period in context (not just for which we have data)
VAR context_first_date = FIRSTDATE(data[Date].[Date])
-- The last day of the period in context (not just for which we have data)
VAR context_last_date = LASTDATE(data[Date].[Date])
-- See if there are any transactions after the beginning of this period
VAR transactions_before_end = CALCULATE(COUNTROWS(data), FILTER(ALL(data), data[Date] <= context_last_date))
-- See if there are any transactions before the end of this period
VAR transactions_after_beginning = CALCULATE(COUNTROWS(data), FILTER(ALL(data), data[Date] >= context_first_date))
-- Find the last date for which there was a transaction
VAR transaction_last_date =
CALCULATE(
MAX(data[Date]),
ALL(data[Date].[Date]),
ALL(data[Date].[Day]),
ALL(data[Date].[Month]),
ALL(data[Date].[MonthNo]),
ALL(data[Date].[Quarter]),
ALL(data[Date].[QuarterNo]),
ALL(data[Date].[Year]),
data[Date] <= context_last_date)
RETURN
IF(
-- If there are either no transactions before the end of this period or none after the start, that means we are in a period
-- for which no data exists (e.g. the future) so show nothing
ISBLANK(transactions_after_beginning) || ISBLANK(transactions_before_end),
BLANK(),
CALCULATE(SUM(data[Total]), data[Date].[Date] = transaction_last_date)
)
Note that at the moment this does not produce a good total when aggregationg all stores together.