cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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

1 ACCEPTED SOLUTION
Super User

@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]``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
5 REPLIES 5
Super User

@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)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

@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.

Super User

@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]``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

@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 🙂

Super User

@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))``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.