Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
From a series of yearly results (loss or profit), I'm trying to calculate yearly taxable income.
The outcome I'm after should be something like this:
Year | Result before taxes | Previous losses compensation | Previous losses balance | Taxable income |
2023 | (84.157,09) | (84.157,09) | ||
2024 | 217.402,26 | (84.157,09) | - | 133.245,17 |
2025 | 62.194,10 | - | - | 62.194,10 |
2026 | (296.847,05) | (296.847,05) | ||
2027 | 1.291.593,25 | (296.847,05) | - | 994.746,19 |
2028 | 1.489.197,68 | - | 1.489.197,68 | |
2029 | (7.000.000,00) | (7.000.000,00) | ||
2030 | 2.245.409,81 | (2.245.409,81) | (4.754.590,19) | - |
2031 | 2.737.866,17 | (2.737.866,17) | (2.016.724,02) | - |
2032 | (5.000.000,00) | (7.016.724,02) | ||
2033 | (1.000.000,00) | (8.016.724,02) | ||
2034 | 4.248.651,56 | (4.248.651,56) | (3.768.072,46) | - |
2035 | 4.567.252,50 | (3.768.072,46) | - | 799.180,04 |
2036 | 4.895.556,20 | - | 4.895.556,20 |
The issue here is that "Previous losses compensation" depend on "Previous losses balance", and viceversa. I've tried with both measures and/or calculated columns, to no avail.
Any suggestion here will be much appreciated. Thanks in advance.
Solved! Go to Solution.
For what it's worth, I think I came out with some sort of solution here. Data lie in [Tabla5], and I defined:
Year's result = SUM(Tabla5[RCAT])
In the first place, I considered that every time there's a positive result immediately after a loss, there must be a compensation:
Last year's loss compensation =
VAR _Comp=
SUMX(Tabla5,
VAR _CurrentResult= [Year's result]
VAR _LastResult=MAXX(FILTER(ALL(Tabla5),Tabla5[Year]=EARLIER(Tabla5[Year])-1),[Year's result])
RETURN
IF(
AND(_LastResult<0, _CurrentResult>0),
MIN(_CurrentResult,ABS(_LastResult)),0
)
)
RETURN
_Comp
Secondly, we need to find out the amount of tax credit available after this first compensation, by means of:
Cumm First compensation =
CALCULATE([Last year's loss compensation], FILTER(ALL(Tabla5),Tabla5[Year]<=MAX(Tabla5[Year])))
and
Prior losses =
SUMX(FILTER(ALL(Tabla5),Tabla5[Year]<MAX(Tabla5[Year])),IF([Year's result]<0,ABS([Year's result]),0))
and
Tax credit available = [Prior losses]-[Cumm First compensation]
The third step would be comparing this tax credit still available to the amount of profit available for compensation:
Profit available for compensation =
IF(
AND([Year's result]>0, [Tax credit available]>0),
[Year's result]-[Last year's loss compensation],0
)
and
Cumm Second Compensation =
MIN(SUMX(FILTER(ALL(Tabla5),Tabla5[Year]<=MAX(Tabla5[Year])),IF(AND([Year's result]>0, [Tax credit available]>0),[Profit available for compensation])),[Tax credit available])
The difference between years of this last measure will bring the value of the current year´s second compensation:
Prior years losses compensation =
[Cumm Second Compensation]- MAXX(FILTER(ALL(Tabla5), Tabla5[Year]=MAX(Tabla5[Year])-1),[Cumm Second Compensation])
Finally, we just need to sum both compensations and substract that value from current year's profit in order to find taxable income:
Total compensation = [Last year's loss compensation]+[Prior years losses compensation]
and
Taxable income =
IF([Year's result]>0, [Year's result]-[Total compensation],0)
The outcome would be something like
I've been trying to buid a one-measure-only solution, but I came across with some row/filter context issues that made it too complicated to me. Maybe someone could sort this out.
¿por qué 2034 y 2035 tienen ingresos negativos imponibles pero 2023 (por ejemplo) no?
For what it's worth, I think I came out with some sort of solution here. Data lie in [Tabla5], and I defined:
Year's result = SUM(Tabla5[RCAT])
In the first place, I considered that every time there's a positive result immediately after a loss, there must be a compensation:
Last year's loss compensation =
VAR _Comp=
SUMX(Tabla5,
VAR _CurrentResult= [Year's result]
VAR _LastResult=MAXX(FILTER(ALL(Tabla5),Tabla5[Year]=EARLIER(Tabla5[Year])-1),[Year's result])
RETURN
IF(
AND(_LastResult<0, _CurrentResult>0),
MIN(_CurrentResult,ABS(_LastResult)),0
)
)
RETURN
_Comp
Secondly, we need to find out the amount of tax credit available after this first compensation, by means of:
Cumm First compensation =
CALCULATE([Last year's loss compensation], FILTER(ALL(Tabla5),Tabla5[Year]<=MAX(Tabla5[Year])))
and
Prior losses =
SUMX(FILTER(ALL(Tabla5),Tabla5[Year]<MAX(Tabla5[Year])),IF([Year's result]<0,ABS([Year's result]),0))
and
Tax credit available = [Prior losses]-[Cumm First compensation]
The third step would be comparing this tax credit still available to the amount of profit available for compensation:
Profit available for compensation =
IF(
AND([Year's result]>0, [Tax credit available]>0),
[Year's result]-[Last year's loss compensation],0
)
and
Cumm Second Compensation =
MIN(SUMX(FILTER(ALL(Tabla5),Tabla5[Year]<=MAX(Tabla5[Year])),IF(AND([Year's result]>0, [Tax credit available]>0),[Profit available for compensation])),[Tax credit available])
The difference between years of this last measure will bring the value of the current year´s second compensation:
Prior years losses compensation =
[Cumm Second Compensation]- MAXX(FILTER(ALL(Tabla5), Tabla5[Year]=MAX(Tabla5[Year])-1),[Cumm Second Compensation])
Finally, we just need to sum both compensations and substract that value from current year's profit in order to find taxable income:
Total compensation = [Last year's loss compensation]+[Prior years losses compensation]
and
Taxable income =
IF([Year's result]>0, [Year's result]-[Total compensation],0)
The outcome would be something like
I've been trying to buid a one-measure-only solution, but I came across with some row/filter context issues that made it too complicated to me. Maybe someone could sort this out.
Thank you for your interest.
Year 2023 is the first in the series: even if it was a profit year, there's no previous result to compensate with.
Years 2034 or 2035 are profit years. In any given profit year, prior negative results can be compensated with positive current result. For instance, in 2024 the whole loss from 2023 is applied; the same goes with 2027 against 2026. Year 2030 makes some different case: current profit is not enough to cover the whole loss from 2029: therefore the excess can be applied to futher years profit.
I hope my answer is clear enough. Should you neeed further clarification, please do not hesitate to ask again.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
2 | |
2 | |
2 | |
2 | |
1 |