Indicate when a row from previous files is missing in newer file

[mattih]

Hi,

I have a SharePoint folder connected data model with a table that has a load of excel files that are added every week. The table looks like this:

Now I would need to make a customn column named "Removed" that shows the datekey when a particular technology is no longer found. For example the technologies Windows Server, Windows Server 2012 and RHEL 6 are still included in date 12.12.2023 but are not present in date 19.12.2023. 

I would like the custom column Removed to show data like this:

Is this possible? Thanks in advance!

[amitchandak · Accepted solution]

@mattih , A new column

Removed =
var _max = minx(filter(Table, [System] = earlier([System]) && [DateKey] > Earlier([DateKey])), [DateKey])
return
if(isblank(minx(filter(Table, [System] = earlier([System]) && [Technology] = earlier([Technology]) && [DateKey] =_max ), [DateKey])), _max, blank())

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820s

[mattih]

Thank you @amitchandak this worked perfectly!