Need help to write a measure in Power BI

[adnanarain]

I am trying to write a measure for current liabilities, i have following data, measure should display the same result as the output column. Calculation is simple min year should display current liability and the following year should display last year liability - the current liability for the same year.

Calendar Year	Current_Liability	Output
2022	$            (935,455.93)	$(935,455.93)
2023	$              (23,317.06)	$(912,138.87)
2024	$            (213,375.68)	$(698,763.19)
2025	$            (608,688.12)	$   (90,075.07)

[Ashish_Excel]

Hi,

Download the PBI file from here.

[SamsonTruong · Accepted solution]

Hi @adnanarain ,

I replicated your test data and was able to achieve the desired output using the following DAX measure:

Output = 
VAR CurrentYear = SELECTEDVALUE('Table'[Calendar Year])
VAR MinYear = CALCULATE(MIN('Table'[Calendar Year]), ALL('Table'))
VAR AllYears =
    FILTER(
        ALL('Table'),
        'Table'[Calendar Year] <= CurrentYear
    )
RETURN
IF (
    ISBLANK(CurrentYear),
    BLANK(),
    SUMX(
        AllYears,
        VAR ThisYear = 'Table'[Calendar Year]
        VAR ThisLiability = 'Table'[Current_Liability]
        VAR PrevLiability =
            CALCULATE(
                SUM('Table'[Current_Liability]),
                'Table'[Calendar Year] = ThisYear - 1
            )
        RETURN
            IF (
                ThisYear = MinYear,
                ThisLiability,
                PrevLiability - ThisLiability
            )
    )
)

Below is the output on my end:

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

[burakkaragoz]

You can achieve this using a recursive DAX measure that builds on the previous year's output. Here's a version that should work based on your logic:

Liability Output = 
VAR currentYear = SELECTEDVALUE('YourTable'[Calendar Year])
VAR currentLiability = SUM('YourTable'[Current Liability])
VAR minYear = CALCULATE(MIN('YourTable'[Calendar Year]), ALL('YourTable'))

RETURN
IF(
    currentYear = minYear,
    currentLiability,
    currentLiability + 
    CALCULATE(
        [Liability Output],
        FILTER(
            ALL('YourTable'),
            'YourTable'[Calendar Year] = currentYear - 1
        )
    )
)

Replace 'YourTable' with your actual table name. This measure checks if the current year is the minimum year and returns the current liability. For other years, it adds the current liability to the previous year's output.

Let me know if you run into any issues with circular dependency or performance. Depending on your model size, there might be other ways to optimize this.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
translation and formatting supported by AI

[adnanarain]

From 3rd year it is giving incorrect result because it is not holding result of the 2nd year

[adnanarain]

@burakkaragozI am using following error measure for Current Liabilities:

Current_Liability = CALCULATE(SUM('GL Entries - ATL2'[Amount]),ALL('ATL - Categories Match File'), 'ATL - Categories Match File'[Financial Category]="Current Liability")

[adnanarain]

@burakkaragoz thank you so much for your reply. this is not giving correct result as we are subtracting the last year value from the current. measure is not holding the last years calculated result