Return a value based upon the latest date from a related table

[KristianA]

Hi,

I've been struggling with writing a DAX statement that will solve this, and would greatly appreciate any help!

I've got two tables, Contracts, which is the first table, and Tariffs, which is the second table.

I'm doing a calculated column named "Last_Main_Product" on the Contracts table, which needs to return the value from the "Product_Name" column on the Tariffs table, for example "Product F" for ID 1-1. I'm only looking to return the Product_Name value for main products, and only the latest one based on the "End_Date" column.

[Zubair_Muhammad · Accepted solution]

Hi @KristianA

try this calculated column

Last_Main_Product =
VAR mylastdate =
    MAXX (
        FILTER (
            Tariffs,
            Tariffs[ID] = Contracts[ID]
                && Tariffs[Customer_ID] = Contracts[Customer_ID]
                && Tariffs[Contract_ID] = Contracts[Contract_ID]
                && Tariffs[Product_Type] = "Main"
        ),
        Tariffs[End_Date]
    )
RETURN
    CALCULATE (
        VALUES ( Tariffs[Produc_Name] ),
        FILTER (
            Tariffs,
            Tariffs[ID] = Contracts[ID]
                && Tariffs[Customer_ID] = Contracts[Customer_ID]
                && Tariffs[Contract_ID] = Contracts[Contract_ID]
                && Tariffs[Product_Type] = "Main"
                && Tariffs[End_Date] = mylastdate
        )
    )

[Zubair_Muhammad · Accepted solution]

@KristianA

Please see Attached Excel File here

[Zubair_Muhammad · Accepted solution]

Hi @KristianA

try this calculated column

Last_Main_Product =
VAR mylastdate =
    MAXX (
        FILTER (
            Tariffs,
            Tariffs[ID] = Contracts[ID]
                && Tariffs[Customer_ID] = Contracts[Customer_ID]
                && Tariffs[Contract_ID] = Contracts[Contract_ID]
                && Tariffs[Product_Type] = "Main"
        ),
        Tariffs[End_Date]
    )
RETURN
    CALCULATE (
        VALUES ( Tariffs[Produc_Name] ),
        FILTER (
            Tariffs,
            Tariffs[ID] = Contracts[ID]
                && Tariffs[Customer_ID] = Contracts[Customer_ID]
                && Tariffs[Contract_ID] = Contracts[Contract_ID]
                && Tariffs[Product_Type] = "Main"
                && Tariffs[End_Date] = mylastdate
        )
    )

[KristianA]

Thanks for the reply!

Your DAX statement succeeded in returning only the main products for each ID, but it doesn't seem to correctly filter it to return the one with the highest date value in the End_Date column.

For example,
for row 1-1 in the contracts table, it returned a Last_Main_Product of "Product B", while it should be "Product F"

[Zubair_Muhammad]

Hi @KristianA

It returns Product F with me.

I used your Sample data

[Zubair_Muhammad · Accepted solution]

@KristianA

Please see Attached Excel File here

[KristianA]

I got it working as well now, thanks! I had to change the data type of the End_Date column, seems the query converted it automatically to text instead of date.

This obviously works, so I accepted it as a solution, but when I tried to modify the statement and applied it to my significantly larger dataset, I got this error "A table of multiple values was supplied where a single value was expected."

I assume I got some duplicates or something messing it up? Can you think of a way to check for errors or some way the statement could handle those duplicate instances?

[Zubair_Muhammad]

Hi @KristianA

One way to deal with duplicates is to use CONCATENATEX

Try this formula

Last_Main_Product =
VAR mylastdate =
    MAXX (
        FILTER (
            Tariffs,
            Tariffs[ID] = Contracts[ID]
                && Tariffs[Customer_ID] = Contracts[Customer_ID]
                && Tariffs[Contract_ID] = Contracts[Contract_ID]
                && Tariffs[Product_Type] = "Main"
        ),
        Tariffs[End_Date]
    )
RETURN
    CONCATENATEX (
        FILTER (
            Tariffs,
            Tariffs[ID] = Contracts[ID]
                && Tariffs[Customer_ID] = Contracts[Customer_ID]
                && Tariffs[Contract_ID] = Contracts[Contract_ID]
                && Tariffs[Product_Type] = "Main"
                && Tariffs[End_Date] = mylastdate
        ),
        Tariffs[Product_Name],
        ","
    )

So if there are more than 1 products that meet the criterion or if there are duplicates this formula would concatenate them

[Zubair_Muhammad · Accepted solution]

@KristianA

Another way is to use the FIRSTNONBLANK or LASTNONBLANK function instead of VALUES

Try this as well

=
VAR mylastdate =
    MAXX (
        FILTER (
            Tariffs,
            Tariffs[ID] = Contracts[ID]
                && Tariffs[Customer_ID] = Contracts[Customer_ID]
                && Tariffs[Contract_ID] = Contracts[Contract_ID]
                && Tariffs[Product_Type] = "Main"
        ),
        Tariffs[End_Date]
    )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( Tariffs[Product_Name], 1 ),
        FILTER (
            Tariffs,
            Tariffs[ID] = Contracts[ID]
                && Tariffs[Customer_ID] = Contracts[Customer_ID]
                && Tariffs[Contract_ID] = Contracts[Contract_ID]
                && Tariffs[Product_Type] = "Main"
                && Tariffs[End_Date] = mylastdate
        )
    )

[KristianA]

Forgot to add a reply here that the solution worked!

I have an additional question regarding product flow, as I'm trying to visualize the flow of product changes.

For example, 1-1 may have been sold in as Product A, then it was changed to Product B, then Product G etc,

Do you know how I could add a column to show the previous active product, or some way of showing all the changes in a table?

The end product I'm looking for is a sankey diagram showing changes over time

[KristianA]

I only have a small sample dataset with me at home, but the CONCATENATE addition seems to have solved the problem! I don't think there are many instances of duplicates, and the report doesn't have to be 100% accurate. If anything, this might be a good way of identifying those duplicates.

I'm going to run some tests on the full dataset when I get to work tomorrow and give you an update 🙂