DAX - A couple of measures depending on each other

Question

From POWER BI table containing a simple series of yearly results (profit or loss), I'm trying to calculate yearly taxable income. For that,

losses from one or several previous years can be deducted from current profit
if the amount of previous losses exceeds the amount of current profit, that excess can be applied to profit
however, the excess of profit over losses can not be applied to further losses

Therefore, taxable income in a loss year is always =0, and always >=0 in a profit year.

The outcome I´m after might be something like this:

Taxable income calculation

The issue here is that "Previous losses compensation" depends on "Previous losses balance" and viceversa, generating a circular dependency. I've tried with both measures and calculated columns, to no avail.

Any suggestion will be very much appreciated. Thanks in advance.

r.adler · Accepted Answer · 2022-06-22T08:58:52.630

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

Outcome

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.

DAX - A couple of measures depending on each other

1 Answers1