How to connect calender table correctly

[Anonymous]

Hi.

How do I create a month-relationship between these tables? I have a column called "Måned"(month) in every table and I have my calender table in the middle but the tables do not react correctly to the calender table in visualization 🙂

Best regards,

Lisa 

[Raj2410]

Hi I have a similar situation my summary table has multiple dates.

My calender table was created and added.

I connected calender date with summary tables work date.

The problem is when I go to visual shown below and filter January .

It filters out the equipments that don't have any date but I want to keep all 1324 equipment evern after month is filtered.

see belwo may be this will explain better.

[Anonymous]

But what if I dont have an actual date for the other tables and I only have the month? 

[FreemanZ]

hi @Anonymous 

you can rebuild a date column. Try add in each tables a calculated column like:

Date =DATE ([Year], [Month], 1)

[Anonymous]

@FreemanZ I dont know if I understand it correctly. I tried to insert your calculated column in different ways but it does not work. 

[FreemanZ · Accepted solution]

hi @Anonymous

Aha, then you need to convert the month name to month numbers first

try like:
Date2 =
VAR _monthnum =
SWITCH(
[monthname],
"January", 1,
"February", 2,
" March", 3,
"April", 4,
"May", 5,
"June", 6,
"July", 7,
"August", 8,
"September", 9,
"October", 10,
"November", 11,
12
)
RETURN
DATE ([Year], _monthnum, 1)

[Anonymous]

@FreemanZ Thank you, that worked! 🙂

[Fowmy]

@Anonymous 
Make sure to have a date type column with month end date for the column "Måned" in each table to which you need to connect the calendar table, then, you should be able to create a One-To-Many relationship.

[FreemanZ]

hi @Anonymous 

because your month column in the calendar table is not unique. 

try to connect all tables with the calendar on date column and always pull dimensional fields from calendar table, like month, year.