Find unique ids that appear in one year and not the other, and vice versa.

[Anonymous]

I'm still sort of new to DAX and I have a problem, wasn't sure exactly where to start.  If I have a situation where I have a table of Sales person ids, and I want to see which ones exist as of this year, but didn't exist in the same set last year, and need an additional measure of sales people that existed last year but no longer do this year, in SQL I can do a Count distinct and exists/not exists to get that. I'm really stuck on where to get started, I keep getting stuck thinking in SQL instead of DAX.  

So just to be clear, if my table was Sales, with a SalepersonId and SalesDate (a few other ones I don't really care about), and I wanted to see which salespeople's id had values for Last year, but not this year and then vice versa, another measure, does anyone have any pointers on where I'd get started?  I was able to use COUNTDISTINCT to get this year, but I can't use PREVIOUSYEAR b/c those people may exist in both and I'm trying to basically get the ones that aren't in both sets for the previous period and the current one if that makes sense?  I've been stumbling around with ALLEXCEPT and a few others but feel I'm just getting more lost.  Anyone have any ideas they'd care to share?

[AlB]

@Anonymous 

Probably best to use a calendar table. If you don't have one, try something like htese measures in card visuals:

In2020NotIn2019 =
COUNTROWS (
    EXCEPT (
        CALCULATETABLE ( DISTINCT ( Table1[ID] ), YEAR ( Table1[SalesDate] ) = 2020 ),
        CALCULATETABLE ( DISTINCT ( Table1[ID] ), YEAR ( Table1[SalesDate] ) = 2019 )
    )
)

In2019NotIn2020 =
COUNTROWS (
    EXCEPT (
        CALCULATETABLE ( DISTINCT ( Table1[ID] ), YEAR ( Table1[SalesDate] ) = 2019 ),
        CALCULATETABLE ( DISTINCT ( Table1[ID] ), YEAR ( Table1[SalesDate] ) = 2020 )
    )
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB · Accepted solution]

@Anonymous 

If you actually want the IDs rather than counting them, create calculate tables:

In2020NotIn2019_T =
    EXCEPT (
        CALCULATETABLE ( DISTINCT ( Table1[ID] ), YEAR ( Table1[SalesDate] ) = 2020 ),
        CALCULATETABLE ( DISTINCT ( Table1[ID] ), YEAR ( Table1[SalesDate] ) = 2019 )
    )

In2019NotIn2020_T =
    EXCEPT (
        CALCULATETABLE ( DISTINCT ( Table1[ID] ), YEAR ( Table1[SalesDate] ) = 2019 ),
        CALCULATETABLE ( DISTINCT ( Table1[ID] ), YEAR ( Table1[SalesDate] ) = 2020 )
    )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers