Forum Discussion
Solved
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...
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]
Greg_Deckler
Community Champion
Community ChampionTerezaPokorna 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]
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))