Forum Discussion
Helper IVYOY CHANGE FOR FIRST YEAR
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.
Super UserHey 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
Helper IVThank 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
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