Forum Discussion

zraopingm's avatar
zraopingm
Icon for Helper IV rankHelper IV
1 year ago
Solved

YOY CHANGE FOR FIRST YEAR

Hello,  image 1, is a matrix report with reporting year in column, with 2025 and 2026 year.. rather than using time intelligence, I attempted to use new visual cal. to get the change between years ...
Need Help
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    Hi zraopingm ,

    Thank you for reaching out to the Microsoft Fabric Community forum.

     

    Please follow below steps.

    1. Created sample table (Table) with sample data.

     

    2. Created Measure (Total Amount) with below DAX code.

    Total Amount = SUM('Table'[TOTAL AMOUNT1])

    3. Created visual calculation with below DAX code.

    YOY CHANGE =
    VAR PrevAmount =
    CALCULATE(
    [Total Amount],
    OFFSET(-1, ORDERBY([Reporting Year]))
    )
    RETURN
    IF(
    ISBLANK(PrevAmount),
    BLANK(),
    [Total Amount] - PrevAmount
    )

    4. Please refer output snap, sample data and attached PBIX file.

     

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

zraopingm's avatar
zraopingm
Icon for Helper IV rankHelper IV
1 year ago

Thank you! 

I did use the measure to solve the issue, but at the same time trying to figure this out in visual cal. for learning.  this is straight downloaded without date table so I used your idea. and got this error message:. 

the goal is yoy change for 2025 should be blank since 2024 is filtered out.  If there is no prev. reporting year, then yoy change is zero or blank.  Thank you for your time 

 

 

 

 

Nasif_Azam's avatar
Nasif_Azam
Icon for Super User rankSuper User
1 year ago

Thanks again for the thoughtful reply! I really appreciate your goal of learning how to use Visual Calculations effectively instead of defaulting to measures it’s an awesome way to push your DAX skills further.

 

The Error:

The error in the screenshot says:

"Column reference cannot be used unless it is guaranteed to be unique in the context."

This usually happens because you're using 'DIR IND CODE' inside a visual calculation context without wrapping it properly. Unlike measures where filters are naturally applied, visual calcs need explicit context scoping to resolve columns like this.

 

Fixing the Visual Calculation:

You’re already using the right idea with OFFSET() and ORDERBY(), but here’s how to fully scope the context:

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

 

Key Fixes:

  • Wrap [DIR IND CODE] in PARTITIONBY() instead of directly referencing it.

  • Make sure both Reporting Year and DIR IND CODE come from a single table (not calculated/renamed columns from other tables).

  • ISINSCOPE() safely blanks out the first year (like 2025) if no previous year exists.

 

Things to Remember:

  • If your data doesn't come from a date table, make sure Reporting Year is a numeric or ordered column otherwise ORDERBY() won’t behave as expected.

  • If TOTAL AMOUNT1 is a measure, you're good. If it's a column, you’ll need to change SUM([TOTAL AMOUNT1]) to match your logic.

  • If you're still getting errors, try using SELECTCOLUMNS or REMOVEFILTERS to ensure the context is clean.

 

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