topic How to get newly added/deleted rows from a table per month/year? in DAX Commands and Tips

How to get newly added/deleted rows from a table per month/year?

afaro — Fri, 29 Mar 2024 15:05:38 GMT

I have a table where it looks like this

c1	c2	date
A1	B1	10-10-24
A2	B2	10-10-24
A3	B3	10-10-24
A2	B2	11-10-24
A4	B4	11-10-24

So, in this example, A1-B1 and A3-B3 were removed from the table during the month of November from October

and A4-B4 was added to the table.

I want two tables which show me these 2 outputs.

I use two columns to identify any unique row c1-c2.

How do I go about doing this?

Thank you in advance.

Re: How to get newly added/deleted rows from a table per month/year?

lbendlin — Fri, 29 Mar 2024 16:13:26 GMT

Read about EXCEPT and INTERSECT.

Re: How to get newly added/deleted rows from a table per month/year?

afaro — Fri, 29 Mar 2024 16:18:18 GMT

My data is in the same table and not separate tables.

Re: How to get newly added/deleted rows from a table per month/year?

lbendlin — Fri, 29 Mar 2024 16:30:36 GMT

Use the date as a filter. In fact, you should have a calendar table for that.

Re: How to get newly added/deleted rows from a table per month/year?

Anonymous — Wed, 03 Apr 2024 07:28:45 GMT

Hi afaro ,

I create a table as you mentioned.

Then I create a calculated column.

Column = 'Table'[c1] & "-" & 'Table'[c2]

I create a measure and here is the DAX code.

Measure = CALCULATE ( COUNT ( 'Table'[Column] ), FILTER ( ALL ( 'Table' ), 'Table'[Column] IN VALUES ( 'Table'[Column] ) && 'Table'[date] = "10/10/2024" ) ) - CALCULATE ( COUNT ( 'Table'[Column] ), FILTER ( ALL ( 'Table' ), 'Table'[Column] IN VALUES ( 'Table'[Column] ) && 'Table'[date] = "11/10/24" ) )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to get newly added/deleted rows from a table per month/year?

afaro — Mon, 08 Apr 2024 14:31:39 GMT

This works but isn't dynamic though. I wanted user to be able to choose month/year