Why my measure won't recalculate with slicers?

[Pawel_C]

Hi Guys!
I have tried to find help with a good old friend Copilot, but he's not clever enough to point me out my mistakes or propose good direction.

Here's the issue. I use the date table in a format as follows for my dashboard:

date	Description	Attribute	ContractDate	Value
29.08.2024	Space Invaders	Game A		111
29.08.2024	Arcanoid	Game B		112
30.08.2024	Space Invaders	Game A		113
30.08.2024	Arcanoid	Game B		114
29.08.2024	Teddy Bear	Toy A	Nov24/Jan25	56
30.08.2024	Teddy Bear	Toy A	Nov24/Jan25	60
29.08.2024	Doll	Toy B	Oct24	65
30.08.2024	Doll	Toy B	Oct24	64
29.08.2024	RC Car	Toy C	Sep24	58
30.08.2024	RC Car	Toy C	Sep24	59

In my visualisation I have slicers to switch between Attributes within the group of products (like switch between Game A and Game B) etc. I need to calculate a spread value between any 'Game' and any 'Toy' and want a measure to give me a proper spread based on which 'Game' or 'Toy' I select in a slicer.
The measure I came up with (and don't work as intended) looks like this:

SpreadLatestQuote = 
VAR LatestGAMEDate = 
    CALCULATE(
        MAX('THIS YEAR'[date]),
        'THIS YEAR'[Attribute] IN {"Game A", "Game B"}
    )
VAR LatestTOYDate = 
    CALCULATE(
        MAX('THIS YEAR'[date]),
        'THIS YEAR'[Attribute] IN {"Toy A", "Toy B", "Toy C"}
    )
VAR GAMEValue = 
    CALCULATE(
        AVERAGE('THIS YEAR'[Value]),
        'THIS YEAR'[Attribute] IN {"Game A", "Game B"},
        'THIS YEAR'[date] = LatestGAMEDate
    )
VAR TOYValue = 
    CALCULATE(
        AVERAGE('THIS YEAR'[Value]),
        'THIS YEAR'[Description] IN {"Toy A", "Toy B", "Toy C"},
        'THIS YEAR'[date] = LatestToyDate
    )
RETURN
    GAMEValue - TOYValue

Caveat is, that not all toys or games are quoted daily, hence comparison between latest date for both.

What's wrong with my measure? Thanks in advance for your feedback

[Pawel_C · Accepted solution]

Thank you for the answer, however I just misspelled "TOY" while I have been anonymizing my code for pasting here. In the real one I don't have VAR name issues. The code itself does not react to the slicer selections.

[bhanu_gautam]

@Pawel_C , Try updated measure

SpreadLatestQuote =
VAR LatestGAMEDate =
CALCULATE(
MAX('THIS YEAR'[date]),
'THIS YEAR'[Attribute] IN {"Game A", "Game B"}
)
VAR LatestTOYDate =
CALCULATE(
MAX('THIS YEAR'[date]),
'THIS YEAR'[Attribute] IN {"Toy A", "Toy B", "Toy C"}
)
VAR GAMEValue =
CALCULATE(
AVERAGE('THIS YEAR'[Value]),
'THIS YEAR'[Attribute] IN {"Game A", "Game B"},
'THIS YEAR'[date] = LatestGAMEDate
)
VAR TOYValue =
CALCULATE(
AVERAGE('THIS YEAR'[Value]),
'THIS YEAR'[Attribute] IN {"Toy A", "Toy B", "Toy C"},
'THIS YEAR'[date] = LatestTOYDate
)
RETURN
GAMEValue - TOYValue