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 colum...
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

     

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