One column - duplicates and unique values in months

[TerezaPokorna]

Hello, I desperately need your help. I have very simple data set that contains of just two columns, date and ID where ID represents a person.

Date	ID
1.1.2022	10101
1.1.2022	10102
1.1.2022	10103
1.1.2022	10104
1.1.2022	10105
1.1.2022	10106
1.1.2022	10107
1.1.2022	10108
1.1.2022	10109
1.2.2022	10101
1.2.2022	10110
1.2.2022	10111
1.2.2022	10102
1.2.2022	10105
1.2.2022	10107

My goal is to figure out, how many IDs differs from the previous month. What I mean is, I know that I have 9 IDs in January, in February I have only 6 IDs. Some of them are duplicates (people that stayed in a company), hovewer some IDs left and some new IDs came. I need a measure that will count me, how many new IDs came and how many IDs left. 

Example: January: 9 IDs

               February: 5 IDs left, 2 new IDs came

               Net change: 6

Measure will be presented in a table for every month, I need to see how many IDs left, how many came and net change. 

Thank you very much for every help.

Tereza

[Greg_Deckler]

@TerezaPokorna I don't understand your 6 for net change, seems like it should be either 3 or 7. See attached PBIX below signature:

New = 
    VAR __CurrentMonth = DISTINCT('Table'[ID])
    VAR __LastMonth = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'),[MonthSort] = MAX([MonthSort]) - 1),"ID",[ID]))
RETURN
    COUNTROWS(EXCEPT(__LastMonth,__CurrentMonth))



Left = 
    VAR __CurrentMonth = DISTINCT('Table'[ID])
    VAR __LastMonth = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'),[MonthSort] = MAX([MonthSort]) - 1),"ID",[ID]))
RETURN
    COUNTROWS(EXCEPT(__CurrentMonth,__LastMonth))



Net Change = 
    VAR __CurrentMonth = DISTINCT('Table'[ID])
    VAR __LastMonth = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'),[MonthSort] = MAX([MonthSort]) - 1),"ID",[ID]))
RETURN
    COUNTROWS(__LastMonth) - COUNTROWS(__CurrentMonth)

[TerezaPokorna]

@Greg_Deckler Thank you so much for your reply, I have tried it and it seems to work pretty well. I can explain net change of 6 = in January I have 9 IDs, 5 IDs left in February (10103, 10104, 10106, 10108, 10109), but 2 new IDs came in February (10110 and 10111). So 9-5+2. I will try it on a large data set, in my original data set I have 10 years of data by months 🙂 Thank you so much for your help.  

[Greg_Deckler · Accepted solution]

@TerezaPokorna Ah, duh. Yeah, I should have figured that out, then Net Change is:

Net Change = 
    VAR __CurrentMonth = DISTINCT('Table'[ID])
    VAR __LastMonth = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'),[MonthSort] = MAX([MonthSort]) - 1),"ID",[ID]))
RETURN
    COUNTROWS(__LastMonth) - [Left] + [New]

[TerezaPokorna]

@Greg_Deckler Dear Greg, I wanted to ask a follow-up question if you don´t mind, you helped me with the problem above and it works nicely, however now I need to figure out if the IDs that came as new do exist in previous IDs. You created a virtual table with people who came as new, however, I need those IDs to compare to the whole history of IDs to know, if they previously existed or if they are completely new 🙂 I am not sure if I explain myself correctly 🙂

[Greg_Deckler]

@TerezaPokorna I think something like:

Net Change = 
    VAR __CurrentMonth = DISTINCT('Table'[ID])
    VAR __Previous = DISTINCT(SELECTCOLUMNS(FILTER(ALL('Table'),"ID",[ID]))
RETURN
    COUNTROWS(EXCEPT(__CurrentMonth, __Previous))