Forum Discussion

alisonpappas's avatar
alisonpappas
Icon for Helper III rankHelper III
6 years ago
Solved

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. W...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    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.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
6 years ago

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's avatar
alisonpappas
Icon for Helper III rankHelper III
6 years ago

Hi Ashish_Mathur 

 

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