PowerPivot return value of last date for each Unique value

[Llew123]

Good day everyone,
So I've been struggling with this calculation in PowerPivot, where I want the formula to return the last project status for each project, from the last date. I have used a few formulas to at least get the last date for each project number, but have not managed to get that right either. I've tried variants of LASTNONBLANK, MAX, LASTDATE etc. but these all miss the crucial bit where it does it for each project number.
=CALCULATE(MAX(GetTimeSheetData[Date]),ALL(GetTimeSheetData)) =LASTNONBLANK(GetTimeSheetData[Date],GetTimeSheetData

)[/FONT]
[FONT=Helvetica Neue].....and many others.[/FONT]
[FONT=Helvetica Neue]I have the file on the link below that shows what I need to do, any help will be most welcome......I'm busy going crazy!![/FONT]
[FONT=Helvetica Neue][URL]https://drive.google.com/file/d/0B29yA0i2Te9ycFVSclBuZUVzSUU/view?usp=sharing[/URL][/FONT]
[FONT=Helvetica Neue]Thanks!![/FONT]
[FONT=Helvetica Neue]Llewellyn[/FONT]

 

[Matt Allington]

DAX will definitely drive you crazy unless you follow good practice. Do you have a calendar table? If not, you should do that first. Then set up a project manager lookup table and a code lookup table. You should set your tables up like I describe here The Optimal Shape for Power Pivot Data -

give your our tankes short meaningful names - this will make the DAX easier to read.

Then in the next question is how are you expecting to see the results of your measures? Set up a pivot table that is laid out they way you want to see the data, then post back.

 

[Llew123]

Hi Matt and thank you for your input. I have added another file which now has the tables and model as you have highlighted.

https://drive.google.com/folderview?id=0B29yA0i2Te9ySVhMRHMxYjBZaTQ&usp=sharing

I have put a table called output, which is what I want to be able to get out of the analysis / calculations.

Please let me know if this is ok.

Thanks and have a great day.

Llewellyn

 

[Matt Allington]

Take a look at my workbook for good design principles and also the answer. Note your need seems simple, but it is actually very complex DAX. I spent many hours solving a similar problem the first time I encountered it.

https://dl.dropboxusercontent.com/u/30711565/PowerPivot DAX Question.xlsx