Dynamically Sum between two datasets with multiple criteria

Hi mattwoldt ,

Please take note that there is an overlap in your second table - 202106 is both the end period of Year 1 and start of Year 2.

To make the calcuation simple, I created several columns to help with the calculations.

Table1

Revenue YrMo = 
VALUE ( Table1[Revenue Year] & FORMAT ( Table1[Revenue Month], "00" ) )

Table2

Start YrMo = 
VALUE ( Table2[Start Year] & FORMAT ( Table2[Start Month], "00" ) )

End  YrMo = 
VALUE ( Table2[End Year] & FORMAT ( Table2[End Month], "00" ) )

For the revenue, you can either use a calculated column or a measure approach.

Calc column in table2

Revenue Sum (Calculated Column) = 
CALCULATE (
    SUM ( Table1[Revenue] ),
    FILTER (
        Table1,
        Table1[Revenue YrMo] >= EARLIER ( Table2[Start YrMo] )
            && Table1[Revenue YrMo] <= EARLIER ( Table2[End  YrMo] )
    ),
    Table1[ID] = EARLIER ( Table2[ID] )
)

Measure

Revenue Sum (Measure) = 
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( Table2, Table2[ID], Table2[Start YrMo], Table2[End  YrMo] ),
        "x",
            CALCULATE (
                SUM ( Table1[Revenue] ),
                FILTER (
                    Table1,
                    Table1[Revenue YrMo] >= [Start YrMo]
                        && Table1[Revenue YrMo] <= [End  YrMo]
                        && Table1[ID] = [ID]
                )
            )
    ),
    [x]
)

Those columns/measure would result to this:

Please refer to this link for the sample pbix - https://drive.google.com/file/d/1_Iv8UAqk58yxQOEDsLFdS7l3fgjxIUOh/view?usp=sharing