Finding deleted names based on date column

[AG36912]

Hello Everyone,

I have a table with 2 columns:

1. Account names and

2. Date (date when the list when generated, ).

The list is generated every month and appended to a master file. I then feed this masterfile to PowerBI desktop.

I want to find out all the Account names (that eventually got got deleted) hence are missing from the list generated in the following month.

Sample Data set (master file):

Acc Name	Date
A	2/25/2022
B	2/25/2022
C	2/25/2022
A	3/25/2022
B	3/25/2022
A	4/25/2022

Desired Result:

Acc Name	Date
C	3/25/2022
B	4/25/2022

What it means is C got deleted in March and B got deleted in April.

Appreciate any help or ideas.

[Ashish_Mathur]

Hi,

This measure works

Max date = if(format(MAX(Data[Date]),"mmyy")=FORMAT(today(),"mmyy"),BLANK(),EDATE(MAX(Data[Date]),1))

Hope this helps.

[AG36912]

Thanks much Ashish. I was able to create the new measure using your code but how do I project these counts (deleted accounts) on a PowerBI Visual month-on-month?

[Ashish_Mathur]

You are welcome.  Please show the expected result very clearly.

[AG36912]

I'm trying to create a visualization wherein I can show a trendline for how many accounts got deleted every month. For eg (based on the sample quoted above): 1 acount ('C') got deleted in March and 1 account ('B') got deleted in April. I'm unable to drag this measure on the value field of the visualization.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.