Count of customers whose rating is lowered more than 3 this year

Question

I am here with a DAX/PBI question.

I have a table that contains Customer number, rating date and rating. (which is shown below). Each customer has several rating notes in different dates, as shown below:

CustomerNumber RatingDate   Rating
887            01.10.2020   2
887            31.12.2019   5
887            06.01.2020   3
887            31.12.2018   3
940            10.07.2020   14
940            31.12.2019   10
656            01.05.2020   8
656            31.12.2019   8

I want to create a measure and place it on a card that shows the count of customers whose rating dropped more than 3 levels in this year (compare between last rating in 2019 and last rating in 2020).

As in this example table, result should be 1 (cus. number 940)

However the measure that i wrote doesnt seem to be working: (blank result)

 COUNTROWS(
FILTER('Rating Change';
 IF(COUNTROWS(FILTER('Rating Change';'Rating Change'[Ratingdate].[Year]=2020))=0;BLANK();
 IF(COUNTROWS(FILTER('Rating Change';'Rating Change'[Ratingdate].[Year]=2019))=0;BLANK();
CALCULATE(MAX('Rating Change'[Rating]);FILTER('Rating Change';'Rating Change'[Ratingdate].[Year]=2019)) - 
CALCULATE(MAX('Rating Change'[Rating]);FILTER('Rating Change';'Rating Change'[Ratingdate].[Year]=2020))>3
)
)

msta42a msta42a · Accepted Answer · 2020-11-04T12:01:35

Probably you have an error in your sample data, because 940 has increased his Level;

The solution could look like this:

  measure = countrows(FILTER (
        ADDCOLUMNS (
            VALUES ( 'Rating Change'[CustomerNumber] ),
            "maxRating",
                CALCULATE (
                    MAX ( 'Rating CHange'[Rating] ),
                    FILTER (
                        ALL ( 'Rating Change'[RatingDate] ),
                        VAR _x =
                            CALCULATE (
                                MAX ( 'Rating Change'[RatingDate] ),
                                YEAR ( 'Rating Change'[RatingDate] ) = YEAR ( TODAY () )
                            )
                        RETURN
                            'Rating Change'[RatingDate] = _x
                    )
                ),
            "maxRatingLY",
                CALCULATE (
                    MAX ( 'Rating CHange'[Rating] ),
                    FILTER (
                        ALL ( 'Rating Change'[RatingDate] ),
                        VAR _x =
                            CALCULATE (
                                MAX ( 'Rating Change'[RatingDate] ),
                                YEAR ( 'Rating Change'[RatingDate] )
                                    = YEAR ( TODAY () ) - 1
                            )
                        RETURN
                            'Rating Change'[RatingDate] = _x
                    )
                )
        ),
        [maxRatingLY] - [maxRating] > 3
    )
    )

Count of customers whose rating is lowered more than 3 this year

1 Answers