How do I create a new Table with two columns from two different tables (No measures)?

[HamidBee]

I am trying to create a new table which contains two columns. Column 1 will calculate the difference between Value 2021 and Value 2020. Column 2 will calculate the percentage change using value 2020 as the base number. I am including the example file below:

https://www.mediafire.com/file/26gimtjb8cshhpx/Example.pbix/file

Thanks in advance. 

[HamidBee]

Hi. I am trying to create a table that looks like the following:

 

For the dates I only really wanted the month to remain and for their to be only one date column. The difference column is the difference between the 2021 and 2020 value. It is already ordered correctly so row 1 -row 1, row 2-row 2 etc.

 

[aj1973]

Hi @HamidBee 

After adding Table Attribute like @ebeery  proposed you can add this Table according to Attribute Granularity

It wouldn't make sense to use Date as the Granularity since the 2 tables (2020 & 2021) are containing different values from 2 different years  

[HamidBee]

I was really only hoping that the month would show as that is what I am comparing. Please see the answer I sent to "ebeery".

[v-easonf-msft · Accepted solution]

Hi, @HamidBee 

You don't need add a new table, you can directly add calculated columns to the table ‘2021’ as follows:

Dates_1 = DATEADD('Calendar'[Date],-1,YEAR)

Value_1 = LOOKUPVALUE('2020'[Value],'2020'[Dates],'2021'[Dates_1],'2020'[Attribute],'2021'[Attribute])

Difference = '2021'[Value]-'2021'[Value_1]

Percentage = IF('2021'[Value_1]=0,BLANK(),'2021'[Difference]/'2021'[Value_1])*100

Result:

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ebeery]

@HamidBee if it's just by attribute that you're looking for, you could do this simply by adding an "Attribute" dimension table to your model, and creating a couple simple measures.

Attributes = DISTINCT(UNION(DISTINCT('2020'[Attribute]),DISTINCT('2021'[Attribute])))

Value 2020 = SUM('2020'[Value])


Value 2021 = SUM('2021'[Value])


Difference = [Value 2021] - [Value 2020]


% Change = DIVIDE([Difference],[Value 2020])

[ebeery]

@HamidBee what is the desired granularity of this new table?  By date and attribute?  Or just by attribute?  Or just total?