Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mrpowrbihelp
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

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!

View solution in original post

3 REPLIES 3
Phil_Seamark
Employee
Employee

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!

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.