Need help multiple rows of data into one unique table and calculation last 30 days

[Sander_NL]

Hi,

I have 2 tables:
1) List of servers that should be backed up (CMDB table)
2) I have a dataset from an API call. This contains multiple lines of data to determine whether a backup has been successful.

Now the server name is unique and I want to know what for each server:
1) The status of the last backup is
2) The date of the last successful backup.
3) How many times the backup has been successful in the last 30 days.

Not every server has been backed up (e.g. new servers) in the last 30 days.

If a server does appear in the table CMDB but does not have a backup status (missed/completed etc) then that status should not be found.

Can someone give me a direction on how to do this?

Example of data

[Zubair_Muhammad · Accepted solution]

@Sander_NL

Last Completed_Backup =
CALCULATE ( MAX ( Blad1[Backup schedule] ), Blad1[Status] = "Completed" )

Number of completed backups = VAR result=CALCULATE(COUNTROWS(Blad1),Blad1[Status]="Completed")
RETURN
IF(ISBLANK(result),0,result)

[Zubair_Muhammad · Accepted solution]

@Sander_NL

your file attached here with these columns

[Zubair_Muhammad]

HI @Sander_NL

First create a relationship between your tables using the common Field i.e. Servers Field

Now you can use these calculated columns

Date last backup =
CALCULATE ( MAX ( Blad1[Backup schedule] ) )

Status Last Backup =
VAR Result =
    CALCULATE (
        FIRSTNONBLANK ( Blad1[Status], 1 ),
        LASTDATE ( Blad1[Backup schedule] )
    )
RETURN
    IF ( ISBLANK ( Result ), "Not Found", Result )

[Zubair_Muhammad · Accepted solution]

@Sander_NL

Last Completed_Backup =
CALCULATE ( MAX ( Blad1[Backup schedule] ), Blad1[Status] = "Completed" )

Number of completed backups = VAR result=CALCULATE(COUNTROWS(Blad1),Blad1[Status]="Completed")
RETURN
IF(ISBLANK(result),0,result)

[Zubair_Muhammad · Accepted solution]

@Sander_NL

your file attached here with these columns

[Sander_NL · Accepted solution]

@Zubair_Muhammad Thank you very much for your quick response. This solution is everything I need.