DAX code to create calculated column based on multiple criteria

[Jules___]

Hi I am struggling with creating a calculated colum to show some data  - i need it to be a calculated colum rather than a measure as I want to use the results in a map with conditional formatting.

here is the issue  I have the following data

location	question category	date	score
london	safety	1/6/23	5
paris	data	1/2/24	0
london	safety	1/2/24	2
madrid	finance	1/2/24	5
london	finance	1/2/24	2
madrid	data	1/6/23	5
paris	safety	1/6/23	5
paris	safety	1/2/24	5
madrid	data	1/6/23	2
london	finance	1/2/24	0

I want to create in a new table the summed score for each location for a particular category but i only want to use the data from the most recent date.

so far i have 

actual = VAR _location = 'table2'[location]

VAR _score =SELECTEDVALUE('table1'[location ])

VAR result =

CALCULATE(SUM('table1'[Score]), FILTER('table 1',table1'[category]="safety"), ALL('table 1'),

'table 1'[location]=_location

)

RETURN

result)

I just cannot work out how to also include date as a variable

many thanks for having a look

[Jules___]

amazing this worked perfectly - thank you so much for your help

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

GROUPBY function (DAX) - DAX | Microsoft Learn

TREATAS function - DAX | Microsoft Learn

expected result table = 
VAR _condition =
    GROUPBY (
        data,
        data[location],
        data[question category],
        "@maxdate", MAXX ( CURRENTGROUP (), data[date] )
    )
VAR _t =
    CALCULATETABLE (
        data,
        TREATAS ( _condition, data[location], data[question category], data[date] )
    )
RETURN
    ADDCOLUMNS (
        SUMMARIZE ( _t, data[location], data[question category], data[date] ),
        "@ScoreSum", CALCULATE ( SUM ( data[score] ) )
    )