cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mrpowrbihelp Frequent Visitor
Frequent Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

Re: Lookup with Max Date

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)    

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

3 REPLIES 3
Phil_Seamark Super Contributor
Super Contributor

Re: Lookup with Max Date

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)      

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

mrpowrbihelp Frequent Visitor
Frequent Visitor

Re: Lookup with Max Date

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 Super Contributor
Super Contributor

Re: Lookup with Max Date

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)    

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 27 members 1,039 guests
Please welcome our newest community members: