How to connect a date column with year?

[bourne2000]

Hi

I have the below table 

Item	Year	Quarter	Value
A	2020	Q1	$274,089.00
A	2020	Q2	$191,325.00
A	2020	Q3	$274,060.00
A	2020	Q4	$324,888.00
B	2020	Q1	$78,949.00
B	2020	Q2	$47,211.00
B	2020	Q3	$75,718.00
B	2020	Q4	$86,363.00
C	2020	Q1	$195,140.00
C	2020	Q2	$144,114.00
C	2020	Q3	$198,342.00
C	2020	Q4	$238,526.00
D	2020	Q1	$81,932.00
D	2020	Q2	$60,593.00
D	2020	Q3	$80,480.00
D	2020	Q4	$91,777.00
E	2020	Q1	$23,015.00
E	2020	Q2	$14,036.00
E	2020	Q3	$20,381.00
E	2020	Q4	$23,635.00
F	2020	Q1	$58,917.00
F	2020	Q2	$46,557.00
F	2020	Q3	$60,099.00
F	2020	Q4	$68,141.00
A	2021	Q1	$315,366.00
A	2021	Q2	$319,847.00
A	2021	Q3	$273,215.00
A	2021	Q4	$336,087.00
B	2021	Q1	$90,077.00
B	2021	Q2	$93,754.00
B	2021	Q3	$81,714.00
B	2021	Q4	$93,416.00
C	2021	Q1	$225,290.00
C	2021	Q2	$226,093.00
C	2021	Q3	$191,500.00
C	2021	Q4	$242,671.00
A	2021	Q1	$92,444.00
A	2021	Q2	$93,697.00

I want to calculate the Quarter to Date. I created a date table, when I connected it's coming many to many. Can anyone advise how to create a date table and connect with only year value?

[Anonymous · Accepted solution]

Hi @bourne2000 ,

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2021, 12, 31 ) ),
    "Year-Quarter",
        YEAR ( [Date] ) & " Q"
            & QUARTER ( [Date] )
)

In the fact table, create a yearquarter column.

Year-Quarter = [Year]&" "&[Quarter]

The relationship is as follows.

It must be a many-to-many relationship, because there are multiple same year-quarter columns in the date table corresponding to multiple year-quarter columns in the fact table.

You can also filter out the year-quarter columns as a table.

Date2 =
DISTINCT (
    SELECTCOLUMNS (
        CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2021, 12, 31 ) ),
        "Year-Quarter",
            YEAR ( [Date] ) & " Q"
                & QUARTER ( [Date] )
    )
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

You should create a seperate table with 2 columns (Quarter and Month Name[the first month of the quarter]).  Create a relationship from the Quarter column of your fact table to the Quarter column of the new table.  Write a RELATED() function in a calculated column to bring over the first month of the quarter in the Fact Table.  In the Fact Table, write this calculated column formula to generate a Date

Date = 1*("1/"&Data[First Month of quarter]&"/"&Data[Year])

Now create a Calendar Table and build a relationship from the Date column of the Fact Table to the Date column of the Calendar Table.

[TheoC]

Hi @bourne2000 

A Date table will always connect to a Fact table using a Date field.  

If you want to calculate quarters, you can use the following:

Measure = CALCULATE ( SUM ( Table[Amount] ) , DATESQTD ( 'Date'[Date] ) )

Hope this helps!

Theo 🙂

[bourne2000]

@TheoC  Thanks for your quick reply. I don't have a date column in my facts table. How to do this?

[TheoC]

Hi @bourne2000 

Unfortunately, with the data you have presented in your table, it is hard to tell you how to add a date to it because your data should have it included.  The other thing is that your table already has the Quarter to Date calculated given there is now lower level of detail that your data table has recorded (i.e. it only has YEAR and the Q1-Q4).  Therefore, the Quarter is already being presented.

Does your data have a Day, Month and Year?

Thanks,

Theo