Forum Discussion
Relationship between Two Tables using Date Column
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 ...
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.
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.
Create on the Date table the column Month
Month = FORMAT([Date], "MMM")&","&'Date'[Year]and put that column into the visual