Calculated Column: Extract max date of related entries

[MaxItaly]

Hello everyone,

I am trying to achieve something that shouldn't be really hard, but I'm not quite capable with LOOKUPVALUE.

I have three tables:

OrderTable, where I have ShippingDate of several articles.

ContractRows, where I have the amount of article that need to be sold.

ContractHeader, where I have information about the contract.

The tables are linked together this way:

[OrderTable]1 - *[ContractRows]1 - *[ContractHeader]

So, for every Order in ContractHeader I have information about what and how much must be sold in ContractRows, and in OrderTables I have information about what has been shipped.

What I'd like to have is a calculated column in ContractHeader, where I have a flag if there has been atleast an order in the past six months.

I guess I should look for the dates in OrderTables linked to the same contract number, extract the max one related to the same contract and check if the date is in the last six months.

My biggest problem is how to extract the maximum order date related to the same contract (if there is one), I should be able to perform the rest.

Maybe I don't even have to use LOOKUPVALUE, but I'm not sure.

May give me a hint?
Many thanks!

[MaxItaly]

Nevermind, I guess it was easier than I thought:

may be

CALCULATE(MAX(ShipDate); RELATEDTABLE(OrderTable)) correct?

[v-yuta-msft · Accepted solution]

Hi MaxItaly,

Create a calculate column and try this pattern:

Flag =
IF (
    COUNTROWS (
        FILTER (
            RELATEDTABLE ( OrderTable ),
            OrderTable[date] >= EDATE ( TODAY (), -6 )
                && OrderTable[date] <= TODAY ()
        )
    ) >= 1,
    "flag",
    BLANK ()
)

Regards,

Jimmy Tao

[MaxItaly]

Thanks, this is good as well, and it includes the second part of my request.

Thanks!