Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jagostinhoCT
Post Partisan
Post Partisan

Average from latest

Hello,

 

Say I have the following sample data for multiple audits of projects.

 

I would like to create a measure for the average of the most recent audits performed to each project. Highlighted in yellow.

 

It feels like it should be something like

Average from Latest = CALCULATE(AVERAGEA(ProjectAuditsDB[Score]),MAX(ProjectAuditsDB[AuditDate]))

 

But I am aware that this may try to pick up the last date of them all. Regardless, it is producing an error.

"A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

 

How can I filter out the last audit date for each project and feed that into the measure?

 

Many thanks

J

 

 

 

Average from the latest.png

3 ACCEPTED SOLUTIONS
Vvelarde
Community Champion
Community Champion

hi @jagostinhoCT

 

1: Create a Summarize Table

 

PROJECTS-LASTAUDIT = SUMMARIZE(Audits;Audits[ProjectID];"AVG-LAST AUDIT"; CALCULATE(AVERAGE(Audits[Score]);filter(Audits;Audits[AuditDate]=Max(Audits[AuditDate]))))

 

2: Create a Measure Average

 

AVG-LASTAUDIT-PROJECTS = AVERAGE('PROJECTS-LASTAUDIT'[AVG-LAST AUDIT])

 

Replace with your fields & Table name

 

The average result is different because you highlighted twice rows of 433PED 8/6 and 14/06

 

EDIT: If you want all in only a measure

 

Medida2 =
AVERAGEX (
    SUMMARIZE (
        Audits;
        Audits[ProjectID];
        "AVERAGE-LASTAUDIT"CALCULATE (
            AVERAGE ( Audits[Score] );
            FILTER ( Audits; Audits[AuditDate] = MAX ( Audits[AuditDate] ) )
        )
    );
    [AVERAGE-LASTAUDIT]
)




Lima - Peru

View solution in original post

Sean
Community Champion
Community Champion

@jagostinhoCT go with @Vvelarde solution from the EDIT:

 

Good job! @Vvelarde

 

Here you can copy this one has same table name and columns (I think) as yours... Good Luck! Smiley Happy

 

Measure =
AVERAGEX (
    SUMMARIZE (
        ProjectAuditsDB,
        ProjectAuditsDB[ProjectID],
        "Average of Last Audit", CALCULATE (
            AVERAGE ( ProjectAuditsDB[Score] ),
            FILTER (
                ProjectAuditsDB,
                ProjectAuditsDB[AuditDate] = MAX ( ProjectAuditsDB[AuditDate] )
            )
        )
    ),
    [Average of Last Audit]
)

View solution in original post

hi @jagostinhoCT

 

Summarize function like his name says create a summary of the table grouping by 1 or several columns and you can use a function to find or calculate something. In the previous measure or table you are grouping all rows by Project ID and calculate the average of every project ID.

 

Now for add a column with the last date for every project id you should add to the formula:

 

SummarizeTable=Summarize(Table; GroupField (In your case: ProjectID);Expresion(The average);"Last Date"; LASTDATE(Audits[AuditDate]))

 

 




Lima - Peru

View solution in original post

19 REPLIES 19

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.