How to use many to one relationships with yearquarter rows

[Anonymous]

I have three datatables: 

1 -  a dates table (having rows with unique values for date (e.g. 2 January 2019) and corresponding quarters (e.g. 2014 Qtr 1), which are not unique.

2 - a table having 1 column with yearquarters (not unique, e.g. 2014 Q1)

3 - a table having 1 column with dates (e.g. 2 January 2019)

In my Dashboards are visuals which need to work well with filtering. However, I cannot get the right relationships. One thing that would work is aggregating table 3 over year quarters and summing the values. How can I do that? 

Or is there another way of solving this? THanks

[ChrisMendoza]

@Anonymous  -

You should not need to have 3 tables with date type things. You should be able to derive YYYY QQQ M & etc from a single date column with all dates.

Possibly provide your file so we may assist.

[Anonymous]

Thank you Chris, how can I share my file? 

[ChrisMendoza]

Publich folder: OneDrive, Dropbox, etc.

[parry2k]

@Anonymous in table 2 even though you have quarter number, just add a first or last date for quarter and both table 2 and table 3 with have relationship with date table on date. With this everything will flow  nicely from date table.

[Anonymous]

Thanks Parry, I think this would work indeed!

Do you know how I can write the piece of DAX that tranfers my yearquarter to a starting date for example? 

e.g. 2014 Q1 -> 01-01-2014

[parry2k]

@Anonymous is this how your quarer formatted? YYYY Qn

[Anonymous]

Yes my year quarter is formatted as: 2017 Q1, 2017 Q2, 2017 Q3, ect. 

[parry2k · Accepted solution]

@Anonymous then it is pretty straight forward, we can convert Q1, Q2, Q3, Q4 to respective month and get a date

go to query editor and advanced editor, add following function in your code just after the let 

 quarterMonth = (quarterNumber) => 
    let
        q = Text.AfterDelimiter( quarterNumber, " "),
        r = if q = "Q1" then 1 else if q = "Q2" then 4 else if q = "Q3" then 7 else 10
    in
        r,

Click done, and now click Custom Column under Add Column menu, and enter following code

#date( 
    Number.FromText( Text.BeforeDelimiter( [Quarter], " ")), 
    quarterMonth( [Quarter] ), 
    1 
    )

you will get corresponding date for each quarter, and change type to Date