Set analysis help

[mahitham]

Hi,

Can any one please help me on below scenario.

I have the Source Data like below

1. Here From Date Field need to create [Report Date] and [Comparision Date] fields like below

[Table]:
LOAD
    "Date"    as   "Report Date",
    ID,
    Name
FROM [lib://App/Add,Remove,Replace.xlsx]
(ooxml, embedded labels, table is Sheet1);

[Alt Table]:
Load
    "Report Date"    as   "Comparision Date"
Resident Table;

2. Now the UI looks like below with Two Filters.

From Report Date need to take only Max date i.e., 13/10/2019

The Requirement is need to compare Max date with other dates data and need to derive how many Names added , Removed and Renamed.

For example when we compare 13/10/2019 with 12/10/2019 , 3 Names added, Name= C deleted, Name = E renamed with M. These Added,Deleted and Renamed count has to show on KPIs and Tables. When user selects 11/10/2019 from comparison date then need to compare  13/10/2019 name with 11/10/2019 names.

I have derived Added count based on below formula

COUNT({$<[Report Date]=P([Report Date])>}DISTINCT Name)
-
COUNT({$<[Report Date]=P([Comparison Date])>}DISTINCT Name)

but I am not able to derive Renamed and Deleted count.Please help me on this.

Please find the below attached excel source.

Thanks in advance.

[sunny_talwar · Accepted solution]

Except removed, the other two can be fixed... check attached....

[mahitham]

Thanks for your Help @sunny_talwar