topic Inflation Rates Adjusting Year of Payout to Loss Year accounting for inflation in DAX Commands and Tips

Inflation Rates Adjusting Year of Payout to Loss Year accounting for inflation

Anonymous — Mon, 20 May 2024 12:09:55 GMT

Hello,

I have been tasked out with creating a dashboard that shows the payout spend compared to the loss year spend, accounting for inflation (year over year, various years). Basically, I want to see if im spending more money today than the loss date. Data and rates below.

Payout Date	Loss Date (Year)	Payout Year	Region	Total	Inflation Rate	Year
1-Jan-24	2023	2024	A	-1547.72	2.10%	2017
1-Jan-24	2023	2024	B	4040.44	2.40%	2018
1-Jan-24	2023	2024	C	104.2	1.80%	2019
1-Jan-24	2023	2024	A	12231	1.20%	2020
1-Jan-24	2019	2024	B	2561.4	4.70%	2021
1-Jan-24	2023	2024	A	27135.18	8.00%	2022
1-Jan-24	2022	2024	B	-12285.27	4.10%	2023
1-Jan-24	2023	2024	A	315123	0.00%	2024
1-Jan-24	2014	2024	B	407
1-Jan-24	2017	2024	A	200206.6
1-Feb-24	2023	2024	B	749.47
1-Feb-24	2023	2024	C	22837.77
1-Feb-24	2017	2024	A	7
1-Feb-24	2023	2024	B	15403.35
1-Feb-24	2024	2024	C	165
1-Feb-24	2023	2024	A	406.98
1-Feb-24	2022	2024	B	31.35
1-Feb-24	2016	2024	C	1986
1-Feb-24	2023	2024	A	7497.12
1-Feb-24	2023	2024	B	12342.62
1-Feb-24	2015	2024	C	4964.75
1-Apr-23	2014	2023	A	3161.5
1-Apr-23	2017	2023	B	6064.65
1-Apr-23	2019	2023	C	492.27
1-Apr-23	2017	2023	C	2628
1-Apr-23	2023	2023	C	72
1-Apr-23	2023	2023	A	4952.46
1-Apr-23	2015	2023	B	200.48
1-May-23	2023	2023	C	68621.64

Re: Inflation Rates Adjusting Year of Payout to Loss Year accounting for inflation

Anonymous — Tue, 21 May 2024 05:15:02 GMT

Hi, Anonymous

Not being very good at economics and failing to understand what you ultimately want to achieve, you can elaborate on the mathematical logic of the result you want to achieve to help solve your problem faster.

Best Regards,
Yang
Community Support Team

Re: Inflation Rates Adjusting Year of Payout to Loss Year accounting for inflation

technolog — Sun, 12 Oct 2025 12:19:35 GMT

use two tables: Payouts and Inflation. Payouts should include Payout Date, Loss Date (Year), Payout Year, Region, and Total. Inflation should include Year and Inflation Rate (as a decimal, for example 0.024 for 2.4%). If the Payout Year isn’t already in your data, create it with YEAR('Payouts'[Payout Date]). Inflation rates are multiplicative, not additive. The cumulative factor from year A to year B is the product of (1 + rate_y) for all years between A+1 and B. To deflate a payout from payout year to loss year, divide by that factor. To inflate, multiply.

You can calculate this directly in DAX. The simplest pattern is: sum the payout amount divided by the cumulative inflation factor for each row. For example, create one measure for nominal payout – SUM('Payouts'[Total]) – and another measure for inflation-adjusted payout. That second measure can be written as:

SUMX('Payouts', VAR LossYear = 'Payouts'[Loss Date (Year)] VAR PayYear = 'Payouts'[Payout Year] VAR Tbl = FILTER('Inflation', 'Inflation'[Year] > LossYear && 'Inflation'[Year] <= PayYear) VAR Factor = EXP(SUMX(Tbl, LN(1 + COALESCE('Inflation'[Inflation Rate], 0)))) RETURN DIVIDE('Payouts'[Total], Factor, 0)). This deflates all payouts to loss-year dollars.

If you want to show the inflation-only uplift percentage, use something like (Nominal / Adjusted) - 1. That will tell you how much of the apparent increase is due to inflation. Display both measures in a matrix by Region and Loss Year – one for the nominal payout, one for the loss-year-adjusted payout. You’ll immediately see which payouts are genuinely higher versus just inflated by time.

For a quick example, a payout in 2024 with a 2019 loss year, using your rates (2020: 1.2%, 2021: 4.7%, 2022: 8.0%, 2023: 4.1%, 2024: 0.0%) gives a cumulative factor around 1.191. A 2561.4 payout in 2024 would equal roughly 2150.2 in 2019 dollars. Missing inflation years can be treated as 0%. Negative payouts are fine, and same-year payouts use factor 1.