How to use aggregation on coalesced columns?

Glad to have helped 🙂

Sure, here's an explanation:

An important general point: Within a visual, the calculation of a measure at "total" level is independent of the calculation of the same measure at individual "row" levels.

The original measure was:

coalesce_measure =
COALESCE (
    SUM ( 'Table A'[value_A] ),
    SUM ( 'Table_B'[value_B] )
)

This measure 

1. Computes sum of value_A
2. Computes sum of value_B
3. Returns the first nonblank of these two values.

For individual dates, this gave you the expected result because the granularity of 'Table A' and 'Table B' happens to be date.

However, at a total level, both of the sums are nonblank because even though dates may be missing, at least one date exists in both 'Table A' and 'Table B'. So the sum of value_A is returned.

In other words, at the total level,

1. Sum of value_A = 11,207
2. Sum of value_B = 9,759
3. COALESCE ( 11,207, 9,759 ) = 11,207

    

The updated measure avoids this issue by iterating over a table of Date values from the dates table using SUMX.

For each date, the sums are calculated and coalesced, then the results for each date are summed.

coalesce_measure by date = 
SUMX (
    VALUES ( dates[date] ), -- dates would also work
    COALESCE( [Value A Sum], [Value B Sum] )
)

For an individual date, the results are the same as the original measure.

But for multiple dates the result is now correct because COALESCE is applied per date.

Here is a relevant article I would recommend reading to understand iterators and row context:

https://www.sqlbi.com/articles/row-context-in-dax/

Hope that helps! 🙂

Regards