PowerBI/SSAS Tabular: How to do calculation group "most recent value" item with measures from multiple fact tables?

Question

To get the most recent value for a measure, one typically does something along these lines:

Sales Total := sum(SalesFact[SalesAmount])

Sales Current :=
var mostrecent = max(SalesFact[Date])

return
calculate(
    [Sales Total],
    SalesFact[Date] = mostrecent

My model has multiple fact tables - SalesFact and OrdersFact, say. So [Orders Current] would return the value for date = max(OrdersFact[Date]).

I'm want to make this logic generic, and put it into a calculation group.

Question: How to implement the "most recent date" part generically? The most recent date for SalesFact need not be the same as the most recent date for OrdersFact.

I can't use the max date from the Calendar table, because the calendar goes far into the future.

I don't know what to try - how can one have a calculation item look up the most recent date from the relevant table?

Answer 1

I strongly doubt this is possible to do as cleanly as you would like because a measure isn't inherently associated with a particular table in general (it can live on one table and reference zero to arbitrarily many other tables in its definition). As a result, the workaround would be to set up an explicit mapping. For example,

MeasureMaxDate =
SWITCH (
    SELECTEDMEASURENAME (),
    "Sales Total", MAX ( SalesFact[Date] ),
    "Orders Total", MAX ( OrdersFact[Date] )
)

Current Sales :=
VAR mostrecent = [MeasureMaxDate]
RETURN
    CALCULATE (
        SELECTEDMEASURE (),
        DateTable[Date] = mostrecent
    )