How to calculate ratio between totals by months in different tables without relationship

[janzitniak]

Hello,

I have a 2 tables without relationship with following data:

Table name 2020 (download)

Date	Type	Amount
2020/01/15	DF	100
2020/01/19	VDD	200
2020/01/20	IUD	300
2020/02/20	IUD	150
2020/02/23	IUD	250
2020/02/27	VDD	350
2020/03/10	DF	200
2020/03/17	DF	300
2020/03/18	VDD	400

Table name 2021 (download)

Date	Type	Amount
2021/01/31	VDD	110
2021/01/31	IUD	220
2021/01/31	VDD	330
2021/02/28	VDD	180
2021/02/28	DF	300
2021/02/28	DF	420
2021/03/31	DF	260
2021/03/31	VDD	390
2021/03/31	IUD	520

I would like to calculate total of amount by every month (january, february, march) for each year (2020 and 2021) and to calculate ratio between years for every month as follow:

(Total amount of 2020 by month / Total amount of 2021 by month) * 100

and to visualise them (table, crosstab or chart).

Regards

Jan

[mahoneypat]

You definitely should append those two tables in the query editor (and this disable load on the two source tables), to simplify your analysis and visualization.

Pat

[janzitniak]

Thank you Pat for your approach and idea.

Jan

[zzcc · Accepted solution]

Hi,   @janzitniak 

To get the month number in each table:

month20 = MONTH('2020'[Date])

Month21 = MONTH('2021'[Date])

 

To create a month table contains each month(1~12), then build 1to many relationship with 20/21 table.

And then to create 3 measures:

mTotal2020 = CALCULATE(SUM('2020'[Amount]),FILTER(ALL('2020'),EOMONTH('2020'[Date],0)=EOMONTH(MAX('2020'[Date]),0)))

mTotal2021 = CALCULATE(SUM('2021'[Total]),FILTER(ALL('2021'),EOMONTH('2021'[Date],0)=EOMONTH(MAX('2021'[Date]),0)))

radio = DIVIDE([mTotal2020],[mTotal2021])

result:

PBIX:

https://drive.google.com/file/d/15_8-X-ZzxEbu-eIeEamcohl43ykZs8Qo/view?usp=sharing

Best Regards

Zerrick

Did I answer your question? Mark my post as a solution!

[janzitniak]

Thank you Zerrick for your useful answer, it works as I expected. 

P.S.: Is it correct If I create in MONTH table a following measure for calculating ratio (it works in same way as yours):

Ratio = SUM('2020'[Amount])/SUM('2021'[Total])

and then is not neccessary to create measures mTotal2020  and mTotal2021 as in your case.

Jan