Need help with the logic to join two tables with incremental data

[lux_sgupta]

Hi All,

I have two tables Master (Date, Id, new_name) and Input(Date, id, input_name). Could you please help me out with the logic in Power BI ( or SQL) so that whenever there is a new input_name in the Input table, the Master Table's new_name should get updated starting the date of entry without changing the previous days name ?

- Date+id is a unique record.

- Master Table overwrites everytime, it doesn't append data.

- Table 1 (date, Id) + Input table (date, id, input_name) = Master table(date,id,new_name)  

I'd appreciate any suggestion. 

 

Thanks,

Shivam

[parry2k]

@lux_sgupta assuming input table will have unique record for id + date for the name, correct? If that is the case, solution is going to be very simple.

[lux_sgupta]

Yes, Date+id is a unique record.

@parry2k  I would also like to mention that Master Table overwrites everytime, it doesn't append data.

Table 1 (date, Id) + Input table (date, id, input_name) = Master table(date,id,new_name)  

[parry2k · Accepted solution]

@lux_sgupta add following column in Master table, change table and column name as per your model

Name =  
CALCULATE( 
    MAX ( 'I Table'[Name] ),  
    TOPN ( 
        1,  
        Filter ( 
            'I Table', 
            'I Table'[id] = 'M Table'[id] && 
            'I Table'[date] <= 'M Table'[date] 
        ), 
        'I Table'[date], 
        DESC 
    ) 
)

[lux_sgupta]

@parry2k  Thanks!