Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Hi Guys

I need to find out the following please; 

 

Reference Table;

Customer NumberMRDR number Price Valid from:Price Valid to: Promo NIV/Case  
111111234510/01/201915/02/20190.42
22222123467/07/201910/08/20190.82
333331234720/02/201919/03/20190.12

 

Table 2

  Customer  L8Pricing entry dateMaterial=mrdrSubtotal 4= NIV
1111121.03.2019123450.50
2222208.07.2019123460.80
3333320.04.2019123470.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,  

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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's avatar
    Anonymous
    6 years ago

    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

5 Replies

  • RobbeVL's avatar
    RobbeVL
    Impactful Individual
    6 years ago

    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

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-lid-msft
        Community Support
        6 years ago

        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,