YOY CHANGE FOR FIRST YEAR

Hey zraopingm ,

Thanks, Zoey! The issue you're facing arises from how you're referencing Reporting Year in your DAX formula using OFFSET and ORDERBY. Let's break this down and fix the issue.

The Core Error
In your third image, the error states: “Reporting Year is not a valid value for the RESET parameter, as it is not present in the visual axes.”

This tells us that while Reporting Year is visible in the matrix, it's not explicitly in the row or column axes of the visual calculation context (especially for OFFSET, which depends on a well-scoped column axis).

Your Current DAX (from Image 2)

YOY CHANGE = 
[TOTAL AMOUNT1] - 
CALCULATE(
    SUM([TOTAL AMOUNT1]), 
    OFFSET(-1, ORDERBY([Reporting Year]))
)

This syntax is almost correct, but OFFSET and ORDERBY only work properly if the column being used ([Reporting Year]) is in the visual axes, and properly scoped for each calculation context.

Fix Approach
You need to add SELECTCOLUMNS and REMOVEFILTERS to better scope the Reporting Year context for the OFFSET function to work correctly.

Try the following revised visual calculation:

YOY CHANGE =
IF(
    ISFILTERED('YourDateTable'[Reporting Year]), 
    [TOTAL AMOUNT1] -
    CALCULATE(
        SUM([TOTAL AMOUNT1]),
        OFFSET(
            -1,
            ORDERBY('YourDateTable'[Reporting Year]),
            PARTITIONBY([DIR IND CODE])
        )
    )
)

Or if you're using a visual calculation in Power BI's Quick Measure interface, try:

YOY CHANGE =
VAR CurrentAmount = [TOTAL AMOUNT1]
VAR PrevAmount =
    CALCULATE(
        [TOTAL AMOUNT1],
        OFFSET(
            -1,
            ORDERBY('YourDateTable'[Reporting Year]),
            PARTITIONBY([DIR IND CODE])
        )
    )
RETURN
    IF(
        ISINSCOPE('YourDateTable'[Reporting Year]) && 
        NOT ISBLANK(PrevAmount),
        CurrentAmount - PrevAmount
    )

Things to Remember:

• Use PARTITIONBY if you want to calculate change within each DIR IND CODE.
• Make sure Reporting Year is a proper column in your Date table and is used consistently in the matrix columns.
• Avoid using Reporting Year directly without scoping when OFFSET is involved.
• ISINSCOPE ensures your first year (2025) stays blank in YOY.

If Still Error:

Ensure:

• The matrix uses 'YourDateTable'[Reporting Year] from the proper date dimension table.

• You do not use a calculated column like YEAR([Date]) from fact tables.

• The column is not renamed in the model, e.g., if it's just "Year" in the model, use that exact name.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

Hello zraopingm 

try this measure

YOY CHANGE =

VAR CurrentYear = SELECTEDVALUE('Reporting Year'[Year])

VAR PrevYear = CurrentYear - 1

VAR CurrentAmount = [TOTAL AMOUNT]

VAR PrevAmount =

    CALCULATE(

        [TOTAL AMOUNT],

        'Reporting Year'[Year] = PrevYear

    )

RETURN

IF(

    ISBLANK(PrevAmount),

    BLANK(),

    CurrentAmount - PrevAmount

)

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

Hi zraopingm 

Please try this:

Previous Year = 
PREVIOUS([Total Revenue], 1, COLUMNS)

YOY =
VAR _PY =
    PREVIOUS ( [Total Revenue], 1, COLUMNS )
RETURN
    IF ( NOT ( ISBLANK ( _PY ) ), [Total Revenue] - _PY )