Forum Discussion

byr10112's avatar
byr10112
Microsoft Employee
4 years ago
Solved

Calculated Column - Previous Month's Data with Multiple Filters

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 IDMonthCategoryScore
100101-02-2022Security.98
100101-02-2022Cost.84
100101-02-2022Other

.70

100101-03-2022Security.91
100101-03-2022Cost.92

 

Desired Table:

Customer IDMonthCategoryScoreLast Month Score
100101-02-2022Security.98 
100101-02-2022Cost.84 
100101-02-2022Other

.70

 

100101-03-2022Security.91.98
100101-03-2022Cost.92.84

 

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

Need Help
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    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

     

1 Reply

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    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