Forum Discussion

massotebernoull's avatar
massotebernoull
Icon for Helper I rankHelper I
4 years ago
Solved

Getting the latest value from two tables

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

CUSTOMERcreatedonSTATUS
A08/03/2021In negociation
A10/02/2021Declined
A10/10/2020Declined
B31/05/2021Approved
B20/05/2021Declined
B08/03/2020Approved
E14/03/2021In negociation
E08/01/2021Declined
E17/08/2020Declined
H22/02/2021Approved
H17/05/2020Declined

 

Appointment table

CUSTOMERVISIT DATE
A15/04/2021
A12/11/2020
B12/06/2021
B09/07/2020
C14/08/2021
D01/01/2021
E04/06/2021
E03/06/2020
F09/08/2021
F21/12/2020
G22/04/2021
G19/05/2020
H08/01/2021
H01/07/2020
I22/02/2021
I06/08/2020
J17/05/2021
J06/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:

CUSTOMERVISIT DATEFROM OPPORTUNITIES
A15/04/2021In negociation
A12/11/2020Declined
B12/06/2021Approved
B09/07/2020Approved
C14/08/2021NO OPPORTUNITIES
D01/01/2021NO OPPORTUNITIES
E04/06/2021In negociation
E03/06/2020Declined
F09/08/2021NO OPPORTUNITIES
F21/12/2020NO OPPORTUNITIES
G22/04/2021NO OPPORTUNITIES
G19/05/2020NO OPPORTUNITIES
H08/01/2021Approved
H01/07/2020Declined
I22/02/2021NO OPPORTUNITIES
I06/08/2020NO OPPORTUNITIES
J17/05/2021NO OPPORTUNITIES
J06/08/2020NO OPPORTUNITIES

 

Any tips?

Need Help
Show and Tell
Tips and Tricks
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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"
    )

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    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"
    )