Filter lastest Date Value

[DSTUART]

Hello all,
I have recently been given the task of transferring Spreadsheet/DB data into POWER BI and unfortunately my DAX skills are weak.
I have a report pulling Audit information from an Oracle table and I have dashboards providing Audit information related to whether a user has read a new version. In the example below 'Open Date' being less than 'Revision Date' indicates the areas of concerns. At the moment the report will show all discrepacies for a user but I only need to see the latest.
I have a Table that contains the following data

Doc No Name Open Date Revision Created Date Read

Doc No		Name	Open Date	Revision Created Date	Read
ABC123		Jo Bloggs	01/02/2016	06/02/2016	FALSE
ABC123		Jo Bloggs	02/02/2016	07/03/2016	FALSE
ABC123		Jo Bloggs	06/02/2016	16/07/2016	FALSE
ABC123		Jo Bloggs	01/03/2016	07/03/2016	FALSE
ABC123		Doug Schmo	01/02/2016	07/02/2016	FALSE
ABC123		Doug Schmo	04/02/2016	16/02/2016	FALSE
ABC123		Doug Schmo	24/02/2016	06/02/2016	TRUE

<tbody>
</tbody><colgroup><col span="3"><col><col><col></colgroup>

What I am trying to achieve is a way to filter the table data to show a unique value for each Name (this being the last "Open Date" and ignoring all earlier dates). I thought I could use the Doc No and Name as a filter and then dispaly the MAX date but so far I have failed miserably.

I have the following set to report if the Open date is less than the version date, indicating that the latest version has not been read.(False).

Read = Audit[Open Date]>=Audit[Revision Created Date].

Apologies for my astounding lack of knowledge but Im trying to self teach at the moment and this is proving tricky.

Any help greatly appreciated.

Dave Stuart

 

[Comfy]

Hi Stuart,

I'm also learning so I hope this is helpful.

Is DAX a necessity?

Are you able to use Power Query first?

Using Power Query you can group by [Name] and [Doc No] and only show the Max Date.

This example filters the data to remove records where [Read] = TRUE

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Doc No", type text}, {"Name", type text}, {"Open Date", type datetime}, {"Revision Created Date", type datetime}, {"Read", type logical}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Read] = false)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Name", "Doc No"}, {{"Last Open", each List.Max([Open Date]), type datetime}})
in
    #"Grouped Rows"

Excel 2010
	A	B	C	D	E
1	Source Data
2	Doc No	Name	Open Date	Revision Created Date	Read
3	ABC123	Jo Bloggs	01/02/2016	##########	FALSE
4	ABC123	Jo Bloggs	02/02/2016	##########	FALSE
5	ABC123	Jo Bloggs	06/02/2016	##########	FALSE
6	ABC123	Jo Bloggs	01/03/2016	##########	FALSE
7	ABC123	Doug Schmo	01/02/2016	##########	FALSE
8	ABC123	Doug Schmo	04/02/2016	##########	FALSE
9	ABC123	Doug Schmo	24/02/2016	##########	TRUE
10
11
12
13
14
15
16	Result
17	Name	Doc No	Last Open
18	Jo Bloggs	ABC123	01/03/2016 00:00
19	Doug Schmo	ABC123	04/02/2016 00:00
Sheet1

 

[DSTUART]

Thanks for Replying. Actually turned out a bit simpler than I thought. I created a measure, Open Date = MAX(Audit[Open Date]) and then used that on the Reports instead. It filters when used with the Name Data and gives the perfect results. Thanks for the help. Very much appreciated.