Forum Discussion
prior negative balances
why does 2034 and 2035 have taxable negative income but 2023 (for example ) does not?
In any case - Power BI has no memory and has no concept of variables. You cannot solve this in DAX. It might be possible to do it in Power Query as List.Accumulate is a bit more powerful than SUMX. But in general you should not be using Power BI for these complex state based computations.
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
_CompSecondly, 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]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.