How to filter on latest version in a table?

[Kahler]

Hi, I've run into a problem in several cases when using Powerpivot on Business system databases. I've tried Google and searches but no luck..
The problem is when I have multiple versions in the same table with a status date or revision number separating them, and I want to pick the newest for each customer, employee etc.

It can look like this:

Employee	Status date	Worktime
Empl 1	2014-01-12	100
Empl 2	2013-04-22	100
Empl 2	2014-08-01	80
Empl 3	2014-02-03	50
Empl 3	2013-04-10	100

<tbody>
</tbody>

In Most cases I want a pivot showing me:

Empl 1	100
Empl 2	80
Empl 3	100

<tbody>
</tbody>

But it would also be good to be able to filter on a date to get the data from that time so for 2014-07-01:

Empl 1	100
Empl 2	100
Empl 3	100

<tbody>
</tbody>

I hope you understand my problem and have a solution, in my mind it shouldn't be that hard, but cant figure it out!

Thanks
/Rickard

 

[Kazlik]

I think if you make these two measures and then make a pivot table with Employee in the rows and the Work Time measure in values it should work.

MaxDate:=MAX(TabName[Status date])
Work Time:=SUMX(DISTINCT(TabName[Employee]),[MaxDate])

 

[Kahler]

Hi that helps me as far as I get the last date, I get "System.FormatException:" but it seems to work.
But how do I filter my column value based on the date in the measure?