Forum Discussion
One column - duplicates and unique values in months
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
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]
5 Replies
- Greg_Deckler
Community Champion
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)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
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]
