Analysis of add/removal rows month-over-month

[tomekm]

Desired output based on the table below: depending on the Month you select in a filter/slicer, identify only the changes (Adds/Removals of the Identifiers) from the Selected Month vs Previous Month. For example, when selecting February in the filter/slicer, I want to show Identifier "E" (i.e. an Add, because "E" isn't present in January), and also "A" and "C" as the Removals (since they are present in January but not in February). Similarly, when I select March in a filter/slicer, I'd like to show "F" and "G" as the Adds (i.e. not present in February), and also "D" and "E" as the Removals (present in February, but not in March). Is there a way to accomplish this specifically with DAX? I'm able to get the results with anti joins in Power Query, but every time I do this monthly analysis I have to create 2 new filtered tables (filtered on the 2 months I need to analyze the changes on) and I don't want to do it this way. I want to keep 1 single 'append-all table' with all the months' data, and be able to identify the adds/removals based on the 2 months I select. I'm sort of looking for a 'dynamic filtering' option combined with anti joins within DAX.

[ryan_mayu]

@tomekm 

maybe you can create a column

COLUMN = 
VAR _last=maxx(FILTER('Table','Table'[Date2]=EDATE(EARLIER('Table'[Date2]),-1)&& 'Table'[identifier]=EARLIER('Table'[identifier])),'Table'[identifier])
return if(ISBLANK(_last),"Add")

 and add that column to filter. 

pls see the attachment below 

[tomekm]

Hi!

Thanks for the reply. This works great to identify the Adds. But how can I also show the Removals in the same formula and Column? See screenshot. By selecting February, I would like the COLUMN to also show the Removals, and maybe label "B" and "D" as "no change"?

Thank you.

[ryan_mayu]

@tomekm 

try this

COLUMN =

VAR _last=maxx(FILTER('Table','Table'[Date2]=EDATE(EARLIER('Table'[Date2]),-1)&& 'Table'[identifier]=EARLIER('Table'[identifier])),'Table'[identifier])

return if(ISBLANK(_last),"Add","no change")

However, regaring the revomals, I am not sure how you want to display. if you selected a month, the identifier only can be new or exists in last month.

[tomekm]

Thank you. This is very helpful. For the Removals, I'm assuming it is not possible to show them in the same column ((by altering the existing COLUMN formula) as the Adds/No changes, since for example Identifier E is either an Add (Feb vs Jan) or a Removal (March vsFeb). In this case, could you please help me "reverse" the Adds formula you provided above, so that it can work the opposite way and identify the Removals? That way I can display 2 tables, one for Adds and one for Removals.

Thank you!

[ryan_mayu · Accepted solution]

@tomekm 

pls try this

Column 2 = 
VAR _last=maxx(FILTER('Table','Table'[Date2]=EDATE(EARLIER('Table'[Date2]),1)&& 'Table'[identifier]=EARLIER('Table'[identifier])),'Table'[identifier])
return if('Table'[Date2]=max('Table'[Date2]),BLANK(),if(ISBLANK(_last),"removals"))

[tomekm]

Great. This works. Thank you!

[ryan_mayu]

you are welcome