Show most recent information

[ShawnPrice]

Below is a memo field I have in our AX 2009 system and I need to be able only show the most current update which should be the top update for each sales person. When I add the memo field in BI I get all of the data in this field. I tried using the First & Last feature in values but it didn't work.  Thanks for the help.

12/08/16 - Sharon still in process of deciding where remaining printers will be shipped to. Once addresses provided to HP, PO's will be cut to TROY. - BM

11/15/16 - Glen working on getting final PO's cut to us for remaining printers that he did not have shipping addresses for. - BM

09/30/16 - Glen advises that the customer has cut both the hardware and consumables PO's. - BM

09/15/16 - Glen advises that HP will sell the hardware directly and the consumables will be put on MWBE paper with indirect Big Deal through HP Source. Working with Glen and team to find best way to drop ship all of the product that will be ordered via multiple PO's. Customer still expecting to cut PO's by September 30. - BM

[v-yuezhe-msft · Accepted solution]

Hi @ShawnPrice,

Firstly, in query editor, add a custom column using the following formula.

Table.AddColumn(Source,"splitcolumn", each Table.FromColumns({Text.Split([Memo], "BM")}))

Secondly, expand your column in query editor and then add the following step in Advanced Editor.

 #"Split Custom.splitcolumn" = Table.SplitColumn(#"Expanded Custom.splitcolumn" ,"Custom.splitcolumn.Column1", Splitter.SplitTextByDelimiter("-"),2)

Thirdly, trim the date column as shown in the following screenshot and change its type to Date.

Apply these changes in Power BI Desktop, then create a column using the formula below and create a report as shown in the following screenshot.

RankColumn = RANKX(FILTER(Table3,EARLIER(Table3[owner])=Table3[owner]),Table3[Custom.splitcolumn.Column1.1])

For more details, please review attached PBIX file.

Thanks,
Lydia Zhang

[Anonymous]

Hi @ShawnPrice, how is the Sales Person captured in the data? Is there a field with the Sales Person's name, or is it simply that their name is included in the memo field? Is there a specific field for the memo date?

I would try using the Top N filter on the Memo Date field to return the top 1 memo by most recent date.

[ShawnPrice]

This is the snapshot of one record in the database.