Achieve connection of custom central calendar to a table's date column

[AG_x11x]

Hi friends,

I would like to create a relationship between my Custom calendar [date] column and [date] column in a separate table in PBI 'Model view'.

Do you have any tips on how to achieve this? 

I currently have created a one to many relationship (1 on calendar side and * on separate table side) (with cross filter direction to "single") and if i try to use "last 6 months" column from custom calendar it doesn't work - all values disappear on any visuals. I updated the cross filter direction to "both" and still didn't do the trick. the [date] column in custom calendar table is just date format so dd/mm/yy and the [date] column in table1 is date/time so dd/mm/yy HH:MM:SS. 

custom calendar is a created table with the script:

Central_Calendar = ADDCOLUMNS(

CALENDAR(DATE(2022,1, 1), DATE(2030,1,1))

, "Year", YEAR([DATE])

, "MonthNumber", MONTH([DATE])

, "Month", FORMAT([DATE], "MMM")

, "Quarter", "Q " & FORMAT([DATE], "Q")

, "QuarterNumber", FORMAT([DATE], "Q")

, "Year Quarter", YEAR([Date]) & " " & "Q" & QUARTER([Date])

, "Year Quarter Sort", YEAR([Date]) & QUARTER([Date])

, "YearMonthNumber", FORMAT([DATE], "yyyy-mm")

, "Month Year", FORMAT([DATE], "MMM YY")

, "Month Year Sort", YEAR([Date]) *100 + MONTH([Date])

, "Fiscal Year", YEAR(EDATE([DATE], 6))

, "FY", "FY" & IF(MONTH([Date])<7,

YEAR([Date])-1 & "/" & FORMAT([Date], "YY"), YEAR([Date]) & "/" & FORMAT(EOMONTH([DATE], 6), "YY"))

, "Fiscal Month", MONTH(EDATE([DATE], 6))

, "Fiscal Quarter", QUARTER(EDATE([DATE], 6))

, "Fiscal Week", WEEKNUM(EDATE([DATE], 6))

, "Last 6 Months", IF(EOMONTH([DATE], 0) > EOMONTH(TODAY(), -6) && [date] <= TODAY(), TRUE())
, "current_fy_to_Date", IF([date] >= DATE(YEAR(TODAY()), 7, 1) && [DATE] <= TODAY(), TRUE(), FALSE()) 
)
custom calendar was inspired by: https://www.youtube.com/watch?v=lTH2C--5xgo

Thanks for your help!

[Anonymous]

Hi @AG_x11x ,

Thanks for reaching out to us with your problem. Base on your description, it seems that the visual can't display the data even though you create the relationship between calendar table and fact table. In order to make further troubleshooting and give you a suitable solution, could you please provide the following info? It would be helpful to find the solution. Thank you.

• Could you please provide some sample data(exclude the sensitive data) in the fact table?
• How did you set your visual?
• Is there any measure applied on the visual? If yes, please provide the related formula.

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards