cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sander_NL Frequent Visitor
Frequent Visitor

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

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

3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

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

@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)
Super User
Super User

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

Sander_NL Frequent Visitor
Frequent Visitor

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

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

4 REPLIES 4
Super User
Super User

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

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 )
Super User
Super User

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

@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)
Super User
Super User

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

Sander_NL Frequent Visitor
Frequent Visitor

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

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 263 members 3,167 guests
Please welcome our newest community members: