Getting the latest value from two tables

[massotebernoull]

Hi, I tried in a different ways, but I'm strugled with this: 

I have the Opportunities table and Appointments table, as it follows:

Opportunities table

CUSTOMER	createdon	STATUS
A	08/03/2021	In negociation
A	10/02/2021	Declined
A	10/10/2020	Declined
B	31/05/2021	Approved
B	20/05/2021	Declined
B	08/03/2020	Approved
E	14/03/2021	In negociation
E	08/01/2021	Declined
E	17/08/2020	Declined
H	22/02/2021	Approved
H	17/05/2020	Declined

Appointment table

CUSTOMER	VISIT DATE
A	15/04/2021
A	12/11/2020
B	12/06/2021
B	09/07/2020
C	14/08/2021
D	01/01/2021
E	04/06/2021
E	03/06/2020
F	09/08/2021
F	21/12/2020
G	22/04/2021
G	19/05/2020
H	08/01/2021
H	01/07/2020
I	22/02/2021
I	06/08/2020
J	17/05/2021
J	06/08/2020

These two tables are related by customer

I need to get the latest value from STATUS column to appointments to create graphs and tables visuals and I'm also need to filter by year, so I think a column dax should allow it.

I must get the latest data from Status column by year, for example:

Customer A has two opportunities in 2022 and one in 2021, and in appointment table, customer A has 2 datas. So it should appear the latest value from 2022 and the value from 2021.

When a costumer doesn't have data in opportunity table, the resulto should appear "No opportunity"

The column in Asspointment table should appear like this:

CUSTOMER	VISIT DATE	FROM OPPORTUNITIES
A	15/04/2021	In negociation
A	12/11/2020	Declined
B	12/06/2021	Approved
B	09/07/2020	Approved
C	14/08/2021	NO OPPORTUNITIES
D	01/01/2021	NO OPPORTUNITIES
E	04/06/2021	In negociation
E	03/06/2020	Declined
F	09/08/2021	NO OPPORTUNITIES
F	21/12/2020	NO OPPORTUNITIES
G	22/04/2021	NO OPPORTUNITIES
G	19/05/2020	NO OPPORTUNITIES
H	08/01/2021	Approved
H	01/07/2020	Declined
I	22/02/2021	NO OPPORTUNITIES
I	06/08/2020	NO OPPORTUNITIES
J	17/05/2021	NO OPPORTUNITIES
J	06/08/2020	NO OPPORTUNITIES

Any tips?

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

From Opportunities CC =
VAR currentyear =
    YEAR ( Appointment[VISIT DATE] )
VAR currentcustomer = Appointment[CUSTOMER]
VAR opptable_bymaxdateyear =
    CALCULATETABLE (
        Opportunities,
        TREATAS (
            SELECTCOLUMNS (
                GROUPBY (
                    ADDCOLUMNS ( Opportunities, "@year", YEAR ( Opportunities[createdon] ) ),
                    Opportunities[CUSTOMER],
                    [@year],
                    "@maxdate_peryear", MAXX ( CURRENTGROUP (), Opportunities[createdon] )
                ),
                "@customer", Opportunities[CUSTOMER],
                "@create", [@maxdate_peryear]
            ),
            Opportunities[CUSTOMER],
            Opportunities[createdon]
        )
    )
VAR filtertable =
    FILTER (
        opptable_bymaxdateyear,
        YEAR ( Opportunities[createdon] ) = currentyear
            && Opportunities[CUSTOMER] = currentcustomer
    )
RETURN
    IF (
        NOT ISBLANK ( MAXX ( filtertable, Opportunities[STATUS] ) ),
        MAXX ( filtertable, Opportunities[STATUS] ),
        "No Opportunity"
    )

[massotebernoull]

Thank you!! It works perfectly