Forum Discussion
Helper IIIrelationships between calendar table and imported data
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.
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.
7 Replies
Hi alisonpappas
Does the calendar table have unique values? Can you show how you create your calendar table and a sample data?
alisonpappasHi 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
Super User
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.
