Finding the latest purchased article date in a table with duplicates using a reference table.

[An_Altruist]

Hello,

I've been having a terrible time with this calculation and I'm ready to stop my endless searching.  My data is setup in this way.  Unique values have *'s.

Article table (the reference table, note the date column can have duplicates, article number unique):

Article Number*   Date Published

A1111                  5/1/2017

A1112                  5/1/2017

A1113                  6/2/2017

A1114                  6/2/2017

A1115                  7/3/2017

A1116                  8/5/2017

A1117                  8/8/2017

Purchases Table (duplicates will exist in all columns, but no one row could be duplicated):

Customer          Article Purchased      Purchased Date

Alpha                 A1111                        9/1/2017

Alpha                 A1112                         9/1/2017

Alpha                 A1114                        10/1/2017

Bravo                 A1116                         8/10/2017

Bravo                 A1117                         9/3/2017

Charlie               A1112                        6/2/2017

Charlie              A1113                           7/20/2017
Charlie              A1114                           8/14/2017

Charlie              A1115                          8/30/2017

Customer Table (Customer name is unique in the table, country is not):

Customer*        Country

Alpha                USA

Bravo                 Canada

Charlie               Canada

Desired result is to know the latest article's publication date by customer.

Customer            Latest Article Publication Date Purchased

Alpha                   6/2/2017           

Bravo                    8/8/2017

Charlie                  7/3/2017

Relationships:

Article table [Article Number] is 1 -> * on Purchases Table [Article Purchased]

Customer Table [Customer] is 1 -> * on Purchases Table [Customer]

I'm not interested in the customer's latest purchase date but rather the latest article publication date from all their purchases.  For example I could show customers that have not read information newer than a July publication date.   

Generally higher article numbers would have more recent publication dates.  I'd prefer not to rely on that but it should be ok if I have to.

It doesn't matter to me what table I put this value in.  It might make sense to do it in the customer table but I'm not picky.

I hope I've described this accurately.  I'm a bit of a novice so I'm hoping it's a calcluated column forumla and not advanced coding.

I greatly appreciate any tips!

[v-caliao-msft · Accepted solution]

@An_Altruist,

Create a calculated column in Purchase table.

ArticlePublishDate = RELATED(Article[DatePublished])

And create a measure in Purchase table.

Measure = CALCULATE(MAX(Purcheases[ArticlePublishDate]),ALLEXCEPT(Purcheases,Purcheases[Customer]))

Add customer and measre to table visual.

Regards,

Charlie Liao

[v-caliao-msft · Accepted solution]

@An_Altruist,

Create a calculated column in Purchase table.

ArticlePublishDate = RELATED(Article[DatePublished])

And create a measure in Purchase table.

Measure = CALCULATE(MAX(Purcheases[ArticlePublishDate]),ALLEXCEPT(Purcheases,Purcheases[Customer]))

Add customer and measre to table visual.

Regards,

Charlie Liao

[An_Altruist · Accepted solution]

Thanks.  I actually had this partially correct but it turns out my article table was incomplete.  This caused my calculated column to come up blank on my first viewable page.  I had so many records that had no proper association I thought the blanks were an indication of a bad formula.

This got me in the right direction.  I used the measure to create some charts but unfortunately I couldn't filter on a measure like I wanted to so I created a calculated column as well.

Latest Purchased Article = calculate(max('Purchases'[ArticlePublishDate]),filter('Purchases','Purchases'[Customer]=earlier('Purchases'[Customer])))

I appreciate the assistance!