Lookup with Max Date

[mrpowrbihelp]

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.

[Phil_Seamark]

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]

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 · Accepted solution]

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)