DAX Measure to get last succeeded backup for each server

[rd1920]

Hello,

I'm having trouble writing measures in Power BI. I need to get the most recent successful backup for each server.

The columns that I have are the following; 

Asset ID = this is the name of the server  ( server 1, server 2, server 3)

Start date time = when the update started ( like 20/01/2024 22:04:00, ..)

end date time = when the update ended ( like 20/01/2024 22:05:00)

Failed after success = this shows if the backup has failed ( 0 means did not fail, 1 means did fail)

I also have a column last backup that should give the last backup date time ( but this is not updated as you can see in the picture ( it sometime claims that the last backup / succesfull backup is 21/01 or 14/01. Then there is also a column that tries to show what the last backup is by 1 ( is last backup) or 0 (is not last backup).

I found out I get 1 row if I search for the newest End Date Time for each specific Asset ID AND it must equal to the date time value inside [Last Successful Backup] for that same row AND Is Last Backup = 1 for that same row . (So highest date for [End Date Time] and [End Date Time] = [Last succesful Backup] and [Is Last Backup] = 1) 

I tried writing severeal measure but I still get multiple rows back for the same Asset ID, that claim to be the Last Succesful Backup ( in the picture Last Restore Point = Las succesfull Backup) 

Can someone help me? Should I write a measure or should I create a calculated row in this table? Could you please write down a step by step guide how to create this measure of column to filter the Asset ID's? Thank you so much in advance! 

Kind regards

[amitchandak]

@rd1920 , Based on what I got, Try two measures like

Last Count = Var _max = maxx(filter( ALLSELECTED(Data1), Data1[AssetID] = max(Data1[AssetID]) && Non(isblank(Data1[Last refresh date])) ),Data1[Last refresh date])
return
CALCULATE(Count(Data1[AssetID]), filter( (Data1), Data1[AssetID] = max(Data1[AssetID]) && Data1[Last refresh date] =_max))

Sum Last Count = sumx(VALUES(Data1[AssetID]) , [Last Count])

Latest Date
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[rd1920]

@amitchandak , the measures you gave do not solve the problem. I currently use this measure: 

Last = var _t = ADDCOLUMNS('Backups',"last",MAXX(FILTER(ALL('Backups'),[Asset ID]=EARLIER([Asset ID])&&[Last Succesfull Backup]>=EARLIER([Last Succesfull Backup])&&[Failed after Success]=0),Backups[Asset ID]))

RETURN MAXX(_t,[last])

But it still does not take the most recent succesful backup that has succeeded. So I still get multiple rows for each server where a backup happend. 

Thank you for your reply.