Create a measure that calculates the difference between two columns on a matrix with filter visuals

[gino_becerra]

I have seen similar needs but I havent been able to find the correct solution. 

I have a ONE data table that lists all the hours by equipment type by date. so i created a matrix that displays the hours in one year and the next (in this case 2024 and 2025). i need a measure that will calculate the difference between the two columns when the data on the columns is dynamic based on a three filter visuals.

can anyone guide me on the solution please. 

[sjoerdvn]

You can also try visual calculations. Note that I added an IF tto supress the values of a diff column for the first year, but it will still show an empty column.

[Anonymous]

Hi @gino_becerra 

Thanks for the reply from Greg_Deckler .

Do you want to modify the column subtotal to be the difference between the largest year in the table and the previous year?

My sample:

If so, you could try this:

Diff = 
VAR _year = MAXX(ALL('Table'), 'Table'[Date].[Year])
VAR _max = CALCULATE(SUM('Table'[Hours]), YEAR('Table'[Date]) = _year)
VAR _min = CALCULATE(SUM('Table'[Hours]), YEAR('Table'[Date]) = _year - 1)
RETURN
IF(
    ISFILTERED('Table'[Date].[Year]),
    SUM('Table'[Hours]),
    _max - _min
)

Put the measure into Values

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gino_becerra]

Thanks for the tip but something is not right. The code for my measure is below. now i think i ommited something. I have a table with the hours and equipment types (Table1) but the dates are coming from a related calendar table (table3). 

also when i add the measure to values it creates another data column....

[Greg_Deckler]

@gino_becerra Maybe:

Measure = 
  VAR __Category = MAX( 'Table1'[Category] )
  VAR __YearMin = YEAR( MIN( 'Table1'[DT Reading] )
  VAR __YearMax = YEAR( MAX( 'Table1'[DT Reading] )
  VAR __Table1 = 
    FILTER( 
        ALLSELECTED( 'Table1' ), [Category] = __Category && YEAR( [DT Reading] ) = __YearMin
    )
  VAR __Table2 = 
    FILTER( 
        ALLSELECTED( 'Table1' ), [Category] = __Category && YEAR( [DT Reading] ) = __YearMax
    )
  VAR __Result = SUMX( __Table2, [Hours] ) - SUMX( __Table1, [Hours] )
RETURN
  __Result