cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
alisonpappas Frequent Visitor
Frequent Visitor

relationships 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: relationships between calendar table and imported data

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7

Re: relationships between calendar table and imported data

Hi @alisonpappas 

 

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

alisonpappas Frequent Visitor
Frequent Visitor

Re: relationships between calendar table and imported data

Hi @darentengdrake ,

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. 

Capture.PNGCaptur 2e.PNG

Re: relationships between calendar table and imported data

Hi @alisonpappas 

 

Can you show me your bridge table as well?

Super User IV
Super User IV

Re: relationships between calendar table and imported data

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
alisonpappas Frequent Visitor
Frequent Visitor

Re: relationships between calendar table and imported data

Hi @Ashish_Mathur 

 

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

alisonpappas Frequent Visitor
Frequent Visitor

Re: relationships between calendar table and imported data

hi @darentengdrake

 

this is the bridge i have as of right now 

 Capture.PNG

Super User IV
Super User IV

Re: relationships between calendar table and imported data

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors