How to Compare to set a condition for two records that must meet a condition for 2 different years?

[gpalomar]

I am working on a data set where we have a list similar to this

id, name, year,earned_savings

A1006, aliance 1, 2021, 0

A1006, aliance 1, 2022, 8,934.253

I'm using the following code

NoMoneyBothYears2 =

IF(

    SUMX(

        FILTER('public mssp_2021_2022_aco', 'public mssp_2021_2022_aco'[year] = 2021 && 'public mssp_2021_2022_aco'[earnsaveloss] <= 0),

        1

    ) > 0 &&

    SUMX(

        FILTER('public mssp_2021_2022_aco', 'public mssp_2021_2022_aco'[year] = 2022 && 'public mssp_2021_2022_aco'[earnsaveloss] <= 0),

        1

    ) > 0,

    "Lost money both years",

    BLANK())

However, it seems to still flag each record.  I would like it to flag only if both lost money on both years, but the script above doesn't seem to be working.

[Anonymous · Accepted solution]

Hi @gpalomar ,

Please have a try.

Create a measure.

NoMoneyBothYears2 =
IF (
    COUNTROWS (
        FILTER (
            'public mssp_2021_2022_aco',
            'public mssp_2021_2022_aco'[year] = 2021
                && 'public mssp_2021_2022_aco'[earnsaveloss] <= 0
        )
    ) > 0
        && COUNTROWS (
            FILTER (
                'public mssp_2021_2022_aco',
                'public mssp_2021_2022_aco'[year] = 2022
                    && 'public mssp_2021_2022_aco'[earnsaveloss] <= 0
            )
        ) > 0,
    "Lost money both years",
    BLANK ()
)

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.