Calculated Column - Previous Month's Data with Multiple Filters

[byr10112]

I have the following table, where the monthly account scores are broken down by category and customer ID. For each ID and category, I would like to get the previous month's data as a calculated column. I tried creating this as a measure but the matrix totals are not accurate, and I was told that creating this as a calculated column can solve my issue. Any help is appreciated:

Current Table:

Customer ID	Month	Category	Score
1001	01-02-2022	Security	.98
1001	01-02-2022	Cost	.84
1001	01-02-2022	Other	.70
1001	01-03-2022	Security	.91
1001	01-03-2022	Cost	.92

Desired Table:

Customer ID	Month	Category	Score	Last Month Score
1001	01-02-2022	Security	.98
1001	01-02-2022	Cost	.84
1001	01-02-2022	Other	.70
1001	01-03-2022	Security	.91	.98
1001	01-03-2022	Cost	.92	.84

I will then be calculating the percent point change in each score. 

[Samarth_18 · Accepted solution]

Hi @byr10112 ,

Please create a column like below:-

Column =
VAR _lastmonth =
    IF ( MONTH ( 'Table (4)'[Month] ) = 1, 12, MONTH ( 'Table (4)'[Month] ) - 1 )
VAR _year =
    IF (
        MONTH ( 'Table (4)'[Month] ) = 1,
        YEAR ( 'Table (4)'[Month] ) - 1,
        YEAR ( 'Table (4)'[Month] )
    )
RETURN
    CALCULATE (
        MAX ( 'Table (4)'[Score] ),
        FILTER (
            ALL ( 'Table (4)' ),
            'Table (4)'[Customer ID] = EARLIER ( 'Table (4)'[Customer ID] )
                && 'Table (4)'[Category] = EARLIER ( [Category] )
                && MONTH ( 'Table (4)'[Month] ) = _lastmonth
                && YEAR ( 'Table (4)'[Month] ) = _year
        )
    )

Output:-

Thanks,

Samarth