Cumulative Sum from Two Different Tables

Hi ahoggatt42 ,

Based on your description, I created three tables and created relationships for them.

Subsequently a measure similar to the one in your description was written and it gave the correct results.

Measure =
VAR test1 =
CALCULATE(SUM('Table A'[Price]),FILTER(ALL('Table A'),'Table A'[date] = MAX('Table A'[date]) && 'Table A'[Type] = "A"))
VAR test2 =
CALCULATE(SUM('Table B'[Price]),FILTER(ALL('Table B'),'Table B'[date] = MAX('Table B'[date]) && 'Table B'[Type] = "A"))
RETURN
test1 + test2

Therefore, please confirm whether the direct relationship between your tables is correct. Another thing to note is that the date of the intermediate table should be placed in the visual object, which is the Bookings Goal table in your description.

Alternatively, there is a way to get the results you want, which requires building a calculation sheet.

Table =
UNION(SELECTCOLUMNS(
FILTER('Table A','Table A'[Type] = "A"),
"MyDate",'Table A'[date],
"MyPrice",'Table A'[Price]),
SELECTCOLUMNS(
FILTER('Table B','Table B'[Type] = "A"),
"MyDate",'Table B'[date],
"MyPrice",'Table B'[Price]))

Then create measure and perform summation.

Measure 2 =
CALCULATE(SUM('Table'[MyPrice]),FILTER('Table','Table'[MyDate] = MAX('Table'[MyDate])))

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Just to go one step further here is a sample of the data I am using.

This is the SO Table:

And Here is the AR Data:

I also created a calendar table (Bookings Goal)

I joined all three tables based on their dates

And using the same measure I used before:

Intl Cumulative UO Bookings (Month) =

var AR =
CALCULATE(
    SUM('AR Data'[Sum of True Price]),
    FILTER(
        ALL('AR Data'),
        'AR Data'[Order Date]<= MAX( 'AR Data'[Order Date]) &&
        'AR Data'[Order Month] = MAX('AR Data'[Order Month])&&
        'AR Data'[Order Year] = MAX('AR Data'[Order Year])&&
        'AR Data'[Order Type] = "UO"
        ))
var SO =
CALCULATE(
    SUM('SO Data'[Sum of True Price]),
    FILTER(
        ALL('SO Data'),
        'SO Data'[Trans Date] <= MAX( 'SO Data'[Trans Date]) &&
        'SO Data'[Trans Month] = MAX('SO Data'[Trans Month])&&
        'SO Data'[Trans Year] = MAX('SO Data'[Trans Year])&&
        'SO Data'[Order Type] = "UO"
        ))

Return
SO+AR

I get this result:

I am expecting that each column is a running sum by date.

I have this working for just one location now without any issues using the same code just without adding another site to it:

Intl Cumulative UO Revenue (Month) =
CALCULATE(
    SUM(HistDetailAll[True Price]),
    FILTER(
        ALL(HistDetailAll),
        HistDetailAll[actshipdate]<= MAX( HistDetailAll[actshipdate]) &&
        HistDetailAll[Actual Month] = MAX(HistDetailAll[Actual Month])&&
        HistDetailAll[Actual Year] = MAX(HistDetailAll[Actual Year]) &&
        HistDetailAll[Order Type] = "UO"
        ))