Forum Discussion
Visual calculation measure pattern
Hi,
What would the pattern of a visual calculation measure in a scenario where there is a hierarchy of Year and then Work type in the row labels of a matrix visual. The objective is to know the amount earned from the same work type in the earlier prior. Please see expected result column.
| Amount | Expected result | |
| 2025-26 | 12 | 57 |
| Work A | 12 | 23 |
| 2024-25 | 57 | 77 |
| Work A | 23 | 21 |
| Work B | 34 | 56 |
| 2019-20 | 77 | |
| Work A | 21 | |
| Work B | 56 |
Hi Ashish_Mathur ,
Still working out the use case for the visual calculations but since it's based on the line values and is not like excel exactly I have created the following calculations:
Previous Year (support) = NEXT([Year]) Previous Year = IF([Previous Year (support)] = [Year],NEXT( [Previous Year (support)]), [Previous Year (support)]) Value = IF( ISINSCOPE([Work]), LOOKUP([Sum of Amoount], [Work], [Work], [Year], [Previous Year]) , LOOKUP([Sum of Amoount], [Year], [Previous Year]) )Then hide the Previous Year columns
Hope this helps you get started, has I said still trying some things and we can do more complex calculations for sure but this simple approach works.
4 Replies
Expected Result Logic:
For each Work Type and Year in the matrix, return the Amount from the same Work Type in the previous Year.
DAX Pattern Summary:
Expected Result =
CALCULATE(
[Amount],
FILTER(
ALL('DateTable'),
'DateTable'[Year] = SELECTEDVALUE('DateTable'[Year]) - 1
),
FILTER(
ALL('Work'),
'Work'[Work Type] = SELECTEDVALUE('Work'[Work Type])
)
)
This works best in a matrix with Year and Work Type in rowsThank you for replying. I need to do this using visual calculations (not measures).
Hi Ashish_Mathur ,
Still working out the use case for the visual calculations but since it's based on the line values and is not like excel exactly I have created the following calculations:
Previous Year (support) = NEXT([Year]) Previous Year = IF([Previous Year (support)] = [Year],NEXT( [Previous Year (support)]), [Previous Year (support)]) Value = IF( ISINSCOPE([Work]), LOOKUP([Sum of Amoount], [Work], [Work], [Year], [Previous Year]) , LOOKUP([Sum of Amoount], [Year], [Previous Year]) )Then hide the Previous Year columns
Hope this helps you get started, has I said still trying some things and we can do more complex calculations for sure but this simple approach works.
Thank you for your help MFelix
