Three routes : SQL Lead/DAX Earlier/Pandas Shift

Relevant Scenario

DEPRECIATION_TABLE
FINAL RATE TABLE DATA

Technique in SQL

Technique in Power BI

Calculated column Effective Until
Result of the formula in the calculated column

Technique in Pandas

depreciation_rate_df[‘effective_until’] =            depreciation_rate_df.groupby([‘country’, ‘asset’])
[‘effective_from’].shift(-1)
depreciation_rate_df[‘effective_until’] = depreciation_rate_df[‘effective_until’].apply(lambda x: x-datetime.timedelta(seconds=1) if ~pd.isnull(x) else None)

Setup Data

create table depreciation_rate
(
asset varchar2(100) NOT NULL,
country varchar2(100) NOT NULL,
rate NUMBER(5,2) NOT NULL,
effective_from date,
effective_until date
);
insert into depreciation_rate (asset, country, rate, valid_from, valid_until)
values ('Automobiles', 'Andora', 12.5, to_date('2019-01-01', 'YYYY-MM-DD'), null);
insert into depreciation_rate (asset, country, rate, valid_from, valid_until)
values ('Building', 'Andora', 4.5, to_date('2019-01-01', 'YYYY-MM-DD'), null);
insert into depreciation_rate (asset, country, rate, valid_from, valid_until)
values ('Automobiles', 'Brunei', 14, to_date('2019-01-01', 'YYYY-MM-DD'), null);
insert into depreciation_rate (asset, country, rate, valid_from, valid_until)
values ('Building', 'Brunei', 6.5, to_date('2019-01-01', 'YYYY-MM-DD'), null);

A final thought:

Certified DS Associate (DP-100, DA-100), pursuing Masters in University Of Michigan’s Applied Data Science Program https://www.linkedin.com/in/simi-talkar/