Merge data into one table using multiple dates

[Anonymous]

Hi,

I am having a few issues with creating one table based on 2 dates and 2 volumes, and wondered if anyone could help, please? 

The tables (below) in isolation are what I want to merge into the one table and as you can see, there are 2 dates (tables = Mort and Comp) and 2 Volumes (tables = Mort and Comp) . 

Based on the 2 tables above, this is how I want my table to output, as you can see, I just have a "year" variable. 

If I build the table and use Mort_Year, the Mort_Vol numbers are correct but the Comp_Vol isn't (and vice versa if I used Comp_Date)

I wasn't sure if I need to concatenate these 2 dates (I wouldn't know how), or is there is somewhere I can put these 2 dates within the table to make it show how I need it too. 

Any help on this would be hugely appreciated. 

[smpa01]

@Anonymous  if you follow the pbix, you will see that it os working as intended. please adapt it to your scenario. I don't have that error in my desrivedTbl and the dat ais based on what you provided.

[Anonymous]

Thank you @smpa01 that has worked. I do have some questions around this though, if I wanted to add more filters into this logic such as:
comp[Comp_Type] and add in another table, Business_Area[Area]

How would I go about doing this please? Happy to start a new thread if that's the best practice 

[smpa01]

@Anonymous  please create a new thread, tag me in the question and most importantly provide sample data and desired output in a table format there and not as image.

[Anonymous]

Create a table for year ( usually we need to have a Date table, but here i assume you have the data at year level ) , which should contain the unique values of years. Define 1 to many relationship from this year table to Mort & Comp tables. 

Pull the year from Year table, Vol fields from corresponding table, it should work. 

If you want, you can create a slicer based on this Year (from year table) field.

Thanks
Raj

[Anonymous]

I have tried to do this so now have a table called 'Year_Table' with variable 'Year'.

However, I have joined this table 1 to many from (Year_table[Year] to MORT[Mort_Year] which is fine. However, I then tried to join (Year_table[Year] to COMP[Comp_Year] but it won't let me due to the followin message:

"an active set of indirect relationships already exists between these tables. To make this relationship active, set the Cross filter to "Single", delete, or deactivate any indirect relationship first"

Any idea how to bypass this please?

[PaulDBrown]

Can you post a depiction of the model (relevant tables) please?

[smpa01 · Accepted solution]

@Anonymous  you can do this

daxDerivedTbl =
Table
    = ADDCOLUMNS (
        ADDCOLUMNS (
            DISTINCT (
                UNION (
                    SELECTCOLUMNS (
                        VALUES ( table1[Mort_year] ),
                        "Generic Year", table1[Mort_year]
                    ),
                    SELECTCOLUMNS (
                        VALUES ( table2[Comp_Year] ),
                        "Generic Year", table2[Comp_Year]
                    )
                )
            ),
            "Mort_vol",
                SUMX (
                    FILTER ( table1, table1[Mort_year] = EARLIER ( [Generic Year] ) ),
                    table1[Mort_vol]
                )
        ),
        "Comp_vol",
            SUMX (
                FILTER ( table2, table2[Comp_Year] = EARLIER ( [Generic Year] ) ),
                table2[Comp_Vol]
            )
    )

[Anonymous]

thanks for your suggestion, I have just tried this but it errors:

Too many arguments were passed to the FILTER Function. The maximum argument count for the function is 2.