Identify latest result and count
What DAX, or combination of DAX, do I need to use to achieve the following please: I have a data source (excel based rn) which lists a project reference number, the date and the RAG status of that project. This data source is going to grow as each new months status updates are gathered together and added to the list. What I would like to do is have a measure for each RAG outcome which tots up the latest update - whether that's all the most recent months entries or from months ago (it could be that there are no updates for a few months on the trot). Data is organised in columns with reference numbers, date of update and RAG outcome (red, amber or green). In a matrix this looks like this: And you can see how some references have an updated RAG for every month, some have gaps and some have none. How do I count the most recent RAG statuses only (regardless of whether june or april for example), one time for each reference number only and for each RAG (3 sep measures)..?? I've tried other solutions to similar queries but relicating them hasn't worked and only given me the single most recent result.
