Forum Discussion

mrpowrbihelp's avatar
mrpowrbihelp
Frequent Visitor
8 years ago
Solved

Lookup with Max Date

Hi,

 

I have a table like below:

 

Date          Model         MSRP Range       New MSRP Column

1/1/15       Box              195-200                       200-205

1/1/15       Glue            225-230                        225-230  

2/1/15       Box              195-200                       200-205

3/1/15       Box              200-205                       200-205

4/1/15       Box              200-205                       200-205

5/1/15       Glue            225-230                         225-230  

 

I need to create a new column that takes the MSRP from the Lastest Date and uses that as the New MSRP.

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    HI mrpowrbihelp

     

    Sorry about that,

     

    Please try this

     

    New  MSRP Column = 
VAR myModel = 'Table1'[Model]
VAR MaxDate = 
    MAXX(
        FILTER(
            'Table1',
            'Table1'[Model] = EARLIER('Table1'[Model])
            ),'Table1'[Date])
RETURN CALCULATE(MAX('Table1'[Price Band]),ALL('Table1'),'Table1'[Date] =  MaxDate ,'Table1'[Model] = myModel)

3 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    HI mrpowrbihelp

     

    This calculated column worked for me

     

    New  MSRP Column = 
VAR MaxDate = 
    MAXX(
        FILTER(
            'Table1',
            'Table1'[Model] = EARLIER('Table1'[Model])
            ),'Table1'[Date])
RETURN CALCULATE(MAX('Table1'[MSRP Range]),ALL('Table1'),'Table1'[Date] =  MaxDate)
    • mrpowrbihelp's avatar
      mrpowrbihelp
      Frequent Visitor
      8 years ago

      Hi Phil_Seamark,

       

      Thank you, I did find your solution to work with the sample data I provided, except when I try to use it with my rows of data something goes wrong.

      For example, even when I take a small piece of data it isn't working. Not sure what is going wrong, below is what I am getting:

       

      Date         Model       Price Band          New MSRP Column

      1/1/16      SIU82        1,500-2,999         1,500-2,999

      1/1/16      SIU22        1,000- 1,499        1,500-2,999

      1/1/17      25652        300-499              500-799

      1/1/17      25652        300-499              500-799

      1/1/17      25652        300-499              500-799

      2/1/17      25652        300-499              500-799

      3/1/17      25652        500-799              500-799

      • Phil_Seamark's avatar
        Phil_Seamark
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        HI mrpowrbihelp

         

        Sorry about that,

         

        Please try this

         

        New  MSRP Column = 
VAR myModel = 'Table1'[Model]
VAR MaxDate = 
    MAXX(
        FILTER(
            'Table1',
            'Table1'[Model] = EARLIER('Table1'[Model])
            ),'Table1'[Date])
RETURN CALCULATE(MAX('Table1'[Price Band]),ALL('Table1'),'Table1'[Date] =  MaxDate ,'Table1'[Model] = myModel)