Average from latest

[jagostinhoCT]

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

[Vvelarde · Accepted solution]

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]
)

[Sean · Accepted solution]

@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!

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]
)

[Vvelarde]

hi @jagostinhoCT

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

[jagostinhoCT]

@Vvelarde

Many thanks for the reply

the following 

= CALCULATE(AVERAGEA(ProjectAuditsDB[BIM Quality Score],FILTER(ProjectAuditsDB,ProjectAuditsDB[AuditDate]=MAX(ProjectAuditsDB[AuditDate]))))

renders this error

"Too many arguments were passed to the AVERAGEA function. The maximum argument count for the function is 1"

What does this mean?

[Vvelarde]

) is missing before the filter

= CALCULATE(AVERAGEA(ProjectAuditsDB[BIM Quality Score]),FILTER(ProjectAuditsDB,ProjectAuditsDB[AuditDate]=MAX(ProjectAuditsDB[AuditDate]))))

[jagostinhoCT]

including the ) produces a different error

The syntax for ')' is incorrect. (DAX(CALCULATE(AVERAGEA(ProjectAuditsDB[BIM Quality Score]),FILTER(ProjectAuditsDB,ProjectAuditsDB[AuditDate]=MAX(ProjectAuditsDB[AuditDate])))))).

[Vvelarde]

Delete ) at the End of the DAX Formula. This error is saying that you have too many ) at the end

[Vvelarde]

MEDIDA =
CALCULATE (
    AVERAGEA ( ProjectAuditsDB[BIM Quality Score] ),
    FILTER (
        ProjectAuditsDB,
        ProjectAuditsDB[AuditDate] = MAX ( ProjectAuditsDB[AuditDate] )
    )
)

[jagostinhoCT]

@Vvelarde

Thank you.

The formula is working. But the calculated value is not correct.

By using the sample data above, it should be 10%. It is calculating it as 14.66%.

I have copy/pasted the sample data in case you want to try it.

Also, do not know if this would be related, when I try to apply the measure to the real dataset it returns an empty value.

ProjectAuditID	ProjectID	AuditDate	Score
433PED_BA07	433PED	14/06/2016	15%
433PPC_BA03	433PPC	13/06/2016	10%
433PED_BA06	433PED	08/06/2016	7%
433PPC_BA02	433PPC	13/06/2016	10%
A630BDP_BA01	A630BDP	25/05/2016	9%
433PED_BA05	433PED	23/05/2016	12%
433PPC_BA01	433PPC	13/06/2016	10%
433LOLH_BA01	433LOLH	16/05/2016	9%

[Vvelarde]

Hi the Value is correct because 14/06/2016 is the max date (your first row) not 13/06/2016.

[jagostinhoCT]

OMG!

Just noticed it now. I am very sorry as I phrased the question wrong.

My goal is to calculate the average by summing the last audit of each project (ProjectID). Highlighted in yellow in the original post.

Does it make sense?

Thank you for all of your help.

[Sean]

@KHorseman there has to be a way to do this without a Summary Table (will give it a go later - just not sure when??? )

I don't feel like doing anything today (its over 100 here)

BTW also let us know if you do try that ImkeF (M solution) in the other post

[Vvelarde · Accepted solution]

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]
)

[jagostinhoCT]

Hello @Vvelarde,

I opted out by using the Summarized Table as I feel it gives me more options.

I am having some trouble trying to understand the SUMMARIZE function syntax.

Is there a way that I can add a column showing the date for the last audits?

Many thanks for your help.

J

[Vvelarde · Accepted solution]

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]))

[jagostinhoCT]

So simple.

Awesome.

Many thanks once again @Vvelarde

[KHorseman]

@Sean I think @Vvelarde's latest edit looks like it's got you covered, unless I'm missing something. I haven't done any real testing though.

I didn't try ImkeF's solution yet because I found a DAX way after all. I still like my original solution. I mean it's bad, it's inefficient, it's hard to tell what the code is doing if you don't already know, and I'm definitely never using it again, but feel like it was at least a unique approach to the problem.

[Sean · Accepted solution]

@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!

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]
)

[jagostinhoCT]

You guys are great!

Many thanks for your time and patience @Vvelarde and @Sean

Am taking the first steps with the software (and the whole data analitics thing, to be honest, as my degree is in architecture. I mean designing buildings and all)

have been learning a lot by self teaching, watching tutorials and help files. But sometimes, most of the times,...ok...90% of the times, it is very hard to figure out on how can it be done. 

Thanks a lot, once again.

J

[Sean]

@KHorseman I've been playing with the Generate Schedule Table again (pretty much all day - since I saw it)

Somebody had a question for Owen and the answer gave me an idea

So I'm experimenting with week numbers now  not enough time for everything I want to try out  

[Sean]

@jagostinhoCT The latest date in the data you posted is June 14 - 15% and its only one value.

You may be thinking of June 13 but that is not the latest data in the sample you posted.

In the original image you posted that first value was June 4 and the latest date then was June 13.

Does this make sense?