Issue with DAX formula. I dont know why MAX is not working.

[radoslaw85]

so I have Power Pivot (in excel) dax Measure.

Dynamika = 
VAR YearMax= MAX(OpCostandReve[Year])
VAR CurrYear = 
    CALCULATE(
        SUM(OpCostandReve[Amount]),
        OpCostandReve[Year] = YearMax
    )
VAR PrevYear = 
    CALCULATE(
        SUM(OpCostandReve[Amount]),
        OpCostandReve[Year] = YearMax - 1
    )
RETURN
DIVIDE(CurrYear  - PrevYear , PrevYear )

Now this my Table:

the issue is that the measure does not show up and im getting errors.

The calc im trying to do is to show % change for cost in 2024 year based on 2023.
So when this measure is p[ut into pivot table the year 2023 should be empty and year 2024 should show % change in cop[arison to previous year.

I know it can be done. I just doint know what im doing wrong here with DAX.

P{lease help.

[v-ssriganesh]

Hello @radoslaw85,

Could you please confirm if your query has been resolved by the provided solution? If so, please mark it as the solution. This will help other community members solve similar problems faster.

Thank you.

[v-ssriganesh]

Hello @radoslaw85,
Just checking in have you been able to resolve this issue? If so, it would be greatly appreciated if you could mark the most helpful reply accordingly. This helps other community members quickly find relevant solutions.
Please don’t forget to “Accept as Solution” and Give “Kudos” if the response was helpful.
Thank you.

[radoslaw85]

thank you for all the advice - I will check your formulas aswell.
that said.
Later that day I found the issue.
I simply forgot that inb excel Power Pivot you have to use := after measure name:
MyMeasure :=
(some Calculation)

and in DAX in power BI normal = is enough. and the formula started to work.
I didnt check yet if my result is correct and here is where I might use yopur formulas.

thank you for the advices.

[v-ssriganesh]

Hello @radoslaw85,
Thank you for sharing your scenario with the Microsoft Fabric Community.

I have reproduced your issue in Power BI using sample data based on your description. The issue with your original measure was due to using a filter like OpCostandReve[Year] = YearMax directly inside CALCULATE(), which doesn’t evaluate as expected in DAX.

Below is the corrected measure that dynamically calculates the year-over-year % cost change, showing a value only for the latest year (e.g: 2024) and blank for previous years:

YoY Cost Change (%) =

VAR YearMax = MAX(OpCostandReve[Year])

VAR CurrYearAmount =

    CALCULATE(

        SUM(OpCostandReve[Amount]),

        FILTER(

            ALL(OpCostandReve),

            OpCostandReve[Year] = YearMax

        )

    )

VAR PrevYearAmount =

    CALCULATE(

        SUM(OpCostandReve[Amount]),

        FILTER(

            ALL(OpCostandReve),

            OpCostandReve[Year] = YearMax - 1

        )

    )

RETURN

IF(

    ISBLANK(PrevYearAmount),

    BLANK(),

    DIVIDE(CurrYearAmount - PrevYearAmount, PrevYearAmount)

)

Expected output in the visual:

The measure dynamically picks the latest year in the visual and compares it with the previous one, giving the desired result. For your reference, I’ve attached a .pbix file where I’ve implemented this logic practically using your scenario.

Thank you, @saritasw for sharing valuable insights.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-ssriganesh]

Hi @radoslaw85,
I hope the information provided has been useful. Please let me know if you need further clarification or would like to continue the discussion.
If your question has been answered, please “Accept as Solution” and Give “Kudos” so others with similar issues can easily find the resolution.
Thank you.

[saritasw]

Hi @radoslaw85 ,
Your issue is that MAX(OpCostandReve[Year]) returns the max year in the current filter context, which can cause errors. Instead, use SELECTEDVALUE to get the current year in the pivot row and calculate the % change like this:

Dynamika =
VAR SelectedYear = SELECTEDVALUE(OpCostandReve[Year])
VAR CurrYearAmount = CALCULATE(SUM(OpCostandReve[Amount]), OpCostandReve[Year] = SelectedYear)
VAR PrevYearAmount = CALCULATE(SUM(OpCostandReve[Amount]), OpCostandReve[Year] = SelectedYear - 1)
RETURN
IF(SelectedYear = MIN(OpCostandReve[Year]), BLANK(), DIVIDE(CurrYearAmount - PrevYearAmount, PrevYearAmount))

This shows blank for 2023 and % change for 2024 compared to 2023.
____________________________________________________________________________________________________________________
If this solution worked for you, kindly mark it as Accept as Solution. This would be helpful for other members who may encounter similar issues and feel free to give a Kudos, it would be much appreciated!
Thank you,
Sarita