cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Deleted/Added Records from previous dataset

Pros, apparently a simple scenerio but didn't find any source. 

 

I populate a table with a dataset I get every month. I need two measure which calculate newly added and newly deleted records. 

what I mean is that a record did exist in previous month but now it does not; and similarly added were the onese found for the first time in the current dataset. Notice that ID 10 does not exist in February as well as March, however February should be the only month where it should be counted.

Capture.PNG

2 REPLIES 2
justinh
Advocate III
Advocate III

Use Merge As New, but choose which merge type. You'll have to do it twice to get the two seperate tables you want. If Table 1 was your old table and Table 2 is your new table, you'll want to choose something like "Rows Only in First Table" to get the deleted rows and "Rows Only In Second Table" to get the newly added rows. 

lbendlin
Super User
Super User

Use the EXCEPT() DAX function

 

Let's say a is the current snapshot and b the previous one.

EXCEPT(a,b)  will give you all the rows that are in a but not in b (ie additions), EXCEPT(b,a) will give you all rows that are in B but not in a (ie deletions)

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors