Hi Guys

[Anonymous]

I need to find out the following please; 

Reference Table;

Customer Number	MRDR number	Price Valid from:	Price Valid to:	Promo NIV/Case
11111	12345	10/01/2019	15/02/2019	0.42
22222	12346	7/07/2019	10/08/2019	0.82
33333	12347	20/02/2019	19/03/2019	0.12

Table 2

Customer  L8	Pricing entry date	Material=mrdr	Subtotal 4= NIV
11111	21.03.2019	12345	0.50
22222	08.07.2019	12346	0.80
33333	20.04.2019	12347	0.43

How can I find out the customer L8 in Table 2 have  values in column " Subtotal 4= NIV" exist in referance table for the same customer between "Price Valid from:" and "Price Valid to:". and if doesn't exisit it through "ERROR" in the row in the result. 

Thank you,  

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

 We can create a calculated table to get the matched value:

Column = 
CALCULATE (
    MAX ( 'Table 2'[Subtotal 4 = NIV] ),
    FILTER (
        'Table 2',
        'Table 2'[Customer L8] = [Customer Number ]
            && 'Table 2'[Pricing entry date] >= [Price Valid from]
            && 'Table 2'[Pricing entry date] <= [Price Valid to]
    )
)

Best regards,

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

 We can create a calculated table to get the matched value:

Column = 
CALCULATE (
    MAX ( 'Table 2'[Subtotal 4 = NIV] ),
    FILTER (
        'Table 2',
        'Table 2'[Customer L8] = [Customer Number ]
            && 'Table 2'[Pricing entry date] >= [Price Valid from]
            && 'Table 2'[Pricing entry date] <= [Price Valid to]
    )
)

Best regards,

[Anonymous]

V-lid-msft, 

thank you for your solution. 

Please how about if I want to look in column "Pricing entry date" for tomorrow dates only if they exist. 

For example if today is 29.01.2020, I want the script to look only for 30.01.2020 between start date and end date from the referance file. 

Can you help please?

Thank you,

Sarmad Dara 

[v-lid-msft]

Hi @Anonymous ,

We can add a condition to meet your requirement:

Column = 
CALCULATE (
    MAX ( 'Table 2'[Subtotal 4 = NIV] ),
    FILTER (
        'Table 2',
        'Table 2'[Customer L8] = [Customer Number ]
            && 'Table 2'[Pricing entry date] >= [Price Valid from]
            && 'Table 2'[Pricing entry date] <= [Price Valid to]
            && 'Table 2'[Pricing entry date] = TODAY() + 1
    )
)

Best regards,

[Anonymous · Accepted solution]

@v-lid-msft , Thank you for your reply, please i noticed you didnt use referance table in your query. when I applied your requery I get an error. 

Note your query in table 1 (reference table) is not used in the query to link the two tables

= CALCULATE
( MAX
( 'Table 2'[Subtotal 4 = NIV] ), FILTER ( 'Table 2', 'Table 2'[Customer L8] = [Customer Number ]
&& 'Table 2'[Pricing entry date] >= [Price Valid from] && 'Table 2'[Pricing entry date] <= [Price Valid to]
&& 'Table 2'[Pricing entry date] = TODAY() + 1 ) )

Thank you,

Sarmad Dara

[RobbeVL]

Hi there, 

Your column names got me a little confused I think.

You are working with 2 tables? You have a table per customer? 

How does the customer Number come in to play? L8? 

Could you provide some more information?

Regards,

Robbe