Project Advance

[KevinH10]

Hi, I have a report about "Public affairs Management" where you can see the advance of the engagement plan actions for a particular issue. I need to display a visual (I was thinking a line chart) where I can see the evolution of the total completion rate of the issue. I need to display the advance month by month of the total completion rate of the issue. For example, in April the total advance of the issue is x%,  June the total advance of the issue is y%.

To clarify, it works this way, there's an issue and that issue has objectives and for each objective there is an engagement plan that consists of actions. 

The table and the expected result is shown in the image below. Basically month by month there is going to be a change in the completion rate of the action. I want to show the total completion rate by issue to the specified date (Month column).

I'm a nooby in the DAX language and with the following measures I get the following results. 

Total completion rate = CALCULATE([Sum Completion Rate],

FILTER('Engagement Plan Progress',
'Engagement Plan Progress'[ID Issue]=EARLIER('Engagement Plan Progress'[ID Issue])),

FILTER('Engagement Plan Progress',
EARLIER('Engagement Plan Progress'[Month No])>='Engagement Plan Progress'[Month No]))

The results I get is because it is filtering all the records of an action, and not the most recent of date by action. But I don't know how to translate that into DAX, I've been trying with MAXX but in calculate I get an error. If any ideas on how to do it efficiently will be accepted too.

Thanks in advance for any ideas on how to solve my problem. 

Kevin Hernández

[v-chuncz-msft]

@KevinH10,

The requirement is not very clear. You may check if RANKX Function helps.

[KevinH10 · Accepted solution]

Thanks for the reply, I used a sum of distinct values with a measure of max values and it worked. 

Regards,

Kevin Hernández

[v-chuncz-msft]

@KevinH10,

Glad to hear that. You may help accept the solution above. Your contribution is highly appreciated.