Visual calculation measure pattern

[Ashish_Mathur]

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

[MFelix · Accepted solution]

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.

[Ashish_Mathur]

Thank you for your help @MFelix 

[Ashish_Mathur]

Thank you for replying.  I need to do this using visual calculations (not measures).

[Shahid12523]

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 rows