Calculate the Average Completion Rate by using the last value of each row

[TomLU123]

Dear Experts,

I have a data set like this. This data set is to capture the completion rate of each task on specific days. 

I wish to calculate the Overvall Completion Rate by using the last percentage of each task. 

In the example above, Overvall Completion Rate = (50%+40%+30%)/3

Is there any expression to create a quick measure to achieve that?

Many thanks!

Best regards,

Tom

[Zubair_Muhammad · Accepted solution]

Hi @TomLU123

Try this approach

First unpivot your date columns from Query Editor...You will get

Now you can use this MEASURE

[Measure ] =
AVERAGEX (
    VALUES ( Table1[Task] ),
    VAR lastnonblankdate =
        CALCULATE ( MAX ( Table1[Date] ), Table1[Value] <> BLANK () )
    RETURN
        CALCULATE ( SUM ( Table1[Value] ), Table1[Date] = lastnonblankdate )
)

[TomLU123]

Hi Zubair, it works! Thank you so much for the help!

[TomLU123]

@Zubair_Muhammad

Hi Zubair,

Just a further check, I have a data set like this. This data set is to capture the completion rate of each task on specific days. But there are empty values since the user havn't update the progress. 

Task	Date	Value
Task 1	1-Jun-18	30%
Task 1	1-Jul-18	40%
Task 1	1-Aug-18
Task 2	1-Jun-18	40%
Task 2	1-Jul-18
Task 2	1-Aug-18
Task 3	1-Jun-18
Task 3	1-Jul-18
Task 3	1-Aug-18

I wish to calculate the Overvall Completion Rate by using the last percentage of each task (including the empty one).

In the example above, Overvall Completion Rate = (40%+40%)/3

Is there any expression to create a quick measure to achieve that?

Many thanks!

Best regards,

Tom

[Zubair_Muhammad]

@TomLU123

See attached file as well