Simple but not able to resolve

[AbdurRahman]

Dear Friends,

I have more than 2 tables, but for demonstration purpose. I m taking only 2 table

Table 1 : Has Sales,

Month	Day	Region	No of Sales
6	2	India	161
6	18	India	167
5	21	India	113
6	1	India	178
6	2	India	156
6	2	India	149
5	24	India	171
5	27	India	149
6	25	India	114
6	2	India	196
6	25	India	158
5	4	India	105
6	27	India	124

Table 2 : Has Order. 

Month	Day	Region	No of order
6	20	India	4
6	27	India	8
5	16	India	1
6	30	India	10
6	7	India	6
6	21	India	4
5	2	India	8
5	20	India	5
6	27	India	6
6	16	India	8
6	21	India	6
5	2	India	5
6	3	India	6

Both tables has DAY, MONTH and records,

I want to have a matrix which can give me below table . On top of this, I would like to have MONTH slicer. (here I have a problem , whenever I took month slicer its giving wrong numbers ). 

In a nut shell, I need below table. 

[v-rzhou-msft]

Hi @AbdurRahman 

You can try my way to build a calendar table and relate all data tables with calendar table by Key column. You need a key column in all tables to build relationships. Here I use Month *100 +Day as Key table.

Calendar Table:

Calendar = ADDCOLUMNS(CALENDAR(DATE(2021,01,01),DATE(2021,06,30)),"Month",MONTH([Date]),"Day",DAY([Date]),"Key",MONTH([Date])*100+DAY([Date]))

Key in Has Order Table.

Key Order = 'Has Order'[Month]*100+'Has Order'[Day]

 

Key in Has Sales Table.

Relationship is as below.

Build a Matrix table as below.

We need to turn on Show on row function in Values.

Use Month column in calendar table to build a slicer. Result is as below.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[AbdurRahman]

Dear RicoZhou,

Thank you so much for solution with detailed explanation, Let me implement this solution with real-time then I will get back to you If i have any obstacles.

Thank you

A

[v-rzhou-msft · Accepted solution]

Hi @AbdurRahman 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[AbdurRahman]

Thank you for you reply. But in real time, I have a huge file. more than one lakh records each file consist so putting them altogether impact performance. However, Now all of them individual files but I moving them all in database and planning to have DIRECT QUERY. 

Could you help me now.

[chajinom]

It is Just an idea, because at least you could do using Power Query before get into PwBI, but also you cand do this using DAX, 
what I'll do is change the name of the value column for something like "Value", I'll add an additional column called number of an there I'll include the description like, No of Sales or No of Order, once the tables are aligned I'll do an UNION on DAX for both tables (It works with as many tables as you need) once the UNION table has been created I'll put as you need to see.

I build the example but I can not load 😄 hope it works,
 Table Has SalesTable Has OrderTable UnionTable Visualization