Forum Discussion
Helper IIIrelationships 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.
Hi alisonpappas
Does the calendar table have unique values? Can you show how you create your calendar table and a sample data?
Helper IIIHi 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.
- alisonpappas
when i try that formula it give me an error saying "the syntax for '4'" is incorrect, please advise
- alisonpappas
hi Anonymous
this is the bridge i have as of right now
- Ashish_Mathur
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.