Using a date column to filter two columns from different tables

[MathiasBI]

Hi!

I'm managing a Power BI report where this company shows their budget versus actual numbers. 

There are three important columns; AccountNo, Budget and Actual.

Budget and Actual are from two different views, while AccountNo is the key connector between them. 

I need a separate date column which can filter these numbers in month and years. I haven't been able to create a column where I get the correct numbers in both columns. It's always either or.

[johnt75]

Create a proper date table, marked as a date table, and link that to both budget and actual tables. Use columns from your date table in your visuals and it should filter everything correctly.

It would also be a good idea to create a separate dimension table for your accounts and link that to both budget and actual tables. It sounds like at the moment you have a many-to-many relationship between the two and that is rarely a good idea.

You can create a table based on the unique values using something like

Account Number =
DISTINCT (
    UNION (
        DISTINCT ( 'Budget'[Account Number] ),
        DISTINCT ( 'Actual'[Account Number] )
    )
)