Combination chart with two separate tables, each having separate date field

Hi EmersonSavage,

First of all you need to create a Date table and create a relationship between the two tables you already have, then assuming that you want to have this by year you need to create the following measures:

Cumulative Net =
VAR datedim =
    MAX ( 'Calendar'[Date] )
RETURN
    TOTALYTD ( SUM ( Valuation[Market Value] ), 'Calendar'[Date] )
        + TOTALYTD ( SUM ( 'Transaction'[Net Contribution] ), 'Calendar'[Date] )

Filter_Non_Used_Dates =
IF (
    SUM ( 'Transaction'[Net Contribution] ) + SUM ( Valuation[Market Value] )
        = 0,
    BLANK (),
    1
)

The just add the first measure to a column bar with the Calendar date in x-axis. second measure (Filter_Non_Used_Dates) is to filter out if you don't wnat to show on your chart the dates that have no transactions.

Regards,

MFelix

Hi EmersonSavage

As an alternative...

1. Create a calculated table as a union of both tables.

Tran-Val = 
UNION
(
    SELECTCOLUMNS
    (
        Valuation;"Account";Valuation[Account];"Security";Valuation[Security];"Market Value";Valuation[Market Value];    "Effective Date";Valuation[Effective Date]
    );
    SELECTCOLUMNS
    (
        'Transaction';"Account";'Transaction'[Account];"Security";'Transaction'[Custom];"Market Value";'Transaction'[Net Contributor];"Effective Date";'Transaction'[Trade Date]
    )
)

NOTE: I created a new blank column in "Transaction" table to get the union correctly. (You can use power query)

2. Create a new calculated column inside Tran-Val table.

Acummulative Value = 

var acc = FIRSTNONBLANK('Tran-Val'[Account];1)
var dat = FIRSTNONBLANK('Tran-Val'[Effective Date];1)
var maxdate = CALCULATE(MAX('Tran-Val'[Effective Date]);ALL('Tran-Val'))
var accumulativeValue = CALCULATE(SUM('Tran-Val'[Market Value]);ALL('Tran-Val');'Tran-Val'[Account] = acc;'Tran-Val'[Effective Date] <= dat)
var valueformax = CALCULATE(SUM('Tran-Val'[Market Value]);ALL('Tran-Val');'Tran-Val'[Account] = acc;'Tran-Val'[Effective Date] = maxdate)

return

IF(dat = maxdate ; accumulativeValue - valueformax;accumulativeValue)

3.Then the final result is like this...

I hope this helps

Regards

BILASolution