Create relationship containing all data for the related columns

[B112]

Hi Everyone!

I need support in creating saes data comparison visual. (Unfortunately I am just a power BI enthusiast)

I have two different excel tables with sales data for two different years (2022,2023).

The tables contains numerouns colums with the same layout/structure. The two column which I wanted to use for the visual is:

2022 sales excel table:

A column: Client ID

B column: Amount

2023 sales excel table:

A column: Client ID

B cloumn: Amount

I want to have a comparison visual table like this:

Client ID   Sales amount 2022   Sales amount 2023

ID12          10 000                       12 0000

ID13          11 000                       13 0000

I set the relationship between the two excel tables in "Modeling" tab under "Manage connections" successfully (cardinality=many to many" and cross filtering options "Sales table 2023 is filtering sales table 2022". 

My problem is that there are records in both excel tables which have no "pair" in the other linked excel tables. For example there are clients which have sales records only in 2023 and nothong in 2022 (or vice versa). With this setup all clients sales records are correctly visualized for 2023 but the ones which have data only for 2022 are not.

How can I create a filtering where all records will be visualized for both years:

For example:

Client ID   Sales amount 2022   Sales amount 2023

ID12          10 000                       12 000

ID13          11 000                       13 000

ID14             0                               2 000

ID11            8 000                           0

Really appreciate any help you can provide! 

[amitchandak]

@B112 , 1.  create common id dimensions and take measures from two tables

2.  append tables, create year dimension and create YOY

ID= distinct(union(all(Sales2022[Product]),all(Sales2023[Product])))

Power BI- Power Query: When I asked you to create common tables: https://youtu.be/PqfGW6pl1Sw

Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...

Append tables and TI

Append Tables (Power Query)
https://www.youtube.com/watch?v=KyXIDInZMxk&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=15

//Only year vs Year, not a level below

This Year = CALCULATE(sum('combined'[sales]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('combined'[sales]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])