Forum Discussion

Ashish_Mathur's avatar
Ashish_Mathur
Super User
11 months ago
Solved

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 am...
Need Help
Show and Tell
  • MFelix's avatar
    MFelix
    11 months ago

    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.

     

     

Shahid12523's avatar
Shahid12523
Community Champion
11 months ago

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