relationships between calendar table and imported data

[alisonpappas]

Hi,

I am trying to make a relationship betweenan excel file I imported and a calendar table I created in Power BI. I have a bunch of data I want to be able to analyze on a rolling period basis. When I attempt to connect the Data table to the Date table it says I am unable to because in order to do Many to One, we need unique values. When I create the Bridge table it is still unable to connect the values together. I am also not able to append or merge any queires because the calendar table was created directly in Power BI and the data was imported from Excel. My actual data is only able to be broken down into YearQuarer (ex: 2015Q3) and not the actual date.

I am looking for help to connect the dates and data together. Unsure if the bridge can be fixed or if there is another way to combine.

[Anonymous]

Hi @alisonpappas 

Does the calendar table have unique values? Can you show how you create your calendar table and a sample data?

[alisonpappas]

Hi @Anonymous ,

The top photo is the calendar I made and the bottom is my data. I want to be able to connect them by "Period" the running monthes-month index were all created by dax formula equations. 

[Ashish_Mathur]

Hi,

From the period column, we will have to first construct a Date.  Assuming the quarters are calendar quarters, try this calculated column formula to create a Date column

= IF(RIGHT(Data[Period],2)="Q1",DATE(LEFT(Data[Period],4),1,1),IF(RIGHT(Data[Period],2)="Q2",DATE(LEFT(Data[Period],4),4,1),IF(RIGHT(Data[Period],2)="Q3",DATE(LEFT(Data[Period],4),7,1),DATE(LEFT(Data[Period],4),10,1))))

Now create a relationship from the this column to the Date column in the Calendar Table.

[alisonpappas]

Hi @Ashish_Mathur 

when i try that formula it give me an error saying "the syntax for '4'" is incorrect, please advise

[Anonymous]

Hi @alisonpappas 

Can you show me your bridge table as well?

[alisonpappas]

hi @Anonymous

this is the bridge i have as of right now 

 

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

=IF(Data[Quarter]="Q1",DATE(Data[Year],1,1),IF(Data[Quarter]="Q2",DATE(Data[Year],4,1),IF(Data[Quarter]="Q3",DATE(Data[Year],7,1),DATE(Data[Year],10,1))))

Hope this helps.