Relationship between Two Tables using Date Column

[Beast]

Hello, I tried to Create a Relationship using Date Column from A Table to B Table and but I am getting below result, where B Table Count is repeated for each Month.

Note: Month Column is from Table A which will also be used for Filter as well. 

Please do help me.

[olgad]

It is a general practice that you create a separate date table for your report.

Many to Many relationship often leads to confusing results. From Date table you can create ONE to MANY reölationships to both tables and use the Date columns in all your visuals and calculations.

The easiest wy to create a date table is to use this expression

Date = CALENDAR (DATE(2013,1,1), DATE(2023,12,31))  or

Date = CALENDAR (DATE(2013,1,1), DATE(Year(Today()),12,31))

depending on your data.

[Beast]

@olgad  As per your suggestion I tried creating the Date Table seperately and Link between two Data Tables.

1. Created Date Column as per your instructions

2. Created One To Many Relation Ship (But Automatically convert as Many to One)

3. Choosen Cross Filter Direction: Single (Even I tried Both)

Result: Now Its not showing any Month Names.

[olgad]

Create on the Date table the column Month

Month = FORMAT([Date], "MMM")&","&'Date'[Year]and put that column into the visual

[Beast]

@olgad Yes I did the same, Not working. Not sure what is the issue. Attached all the screen shots.

Date Table Forumula: Calendar = CALENDAR(DATE(2022,1,1),DATE(2023,12,31))

Month Column Formula = Month = FORMAT([Date], "MMM" &","& 'Calendar'[Date].[Year])

[olgad]

Hi, check if the relationship is active, what is the column in Table B that you are using for linking? Can you take a snapshot from Manage Relationship Window?

[Beast]

@olgad  Thanks for your response. The issue occurs when you put the data in same table. 

Date and Count from Table A + Count from Table B -> In Same Table. as show in screenshot above.

In below screen shot - Main Table is Table A and Report is Table B. I created a relationship between startedDate - Created Date. In Cross Filter direction, I have tried all 3 options and still same results.