Total Sum of all Data but Filtering out Max Date?

[Anonymous]

Hello,

Please help! 

I am working on a dashboard and it was requested that I provide a tab that lists the total sales of a customer (in both of our market segments) as well as providing a list of customers that we have in our system that has not purchased anything from us, or those that have purchased from us, but not for a long time. This way, our salespeople know who to target. I am running into issues when I tried to complete this myself.

Here's an example:

Customer 0123: Purchase 1 for $2,000 on Jan 18 2017 from Market 1. Purchase 2 for $10,000 on Feb 22, 2018 from Market 2. Purchase 3 for $20,000 on Sept 25, 2019 from Market 1. Desired Result: Total Sales: $32,000 - Last Sell Date - Sept 25, 2019. Total Market 1 Sales: $22,000 - Last Sell Date: Sept 25, 2019. Total Market 2 Sales: $10,000 - Last Sell Date: Feb 22, 2018.

Customer 4567: Purchase 1 for $2,000 on Jan 18 2008 from Market 2. Purchase 2 for $10,000 on Feb 22, 2009 from Market 1. Purchase 3 for $20,000 on Sept 25, 2010 from Market 2. Desired Result: Total Sales: $32,000 - Last Sell Date - Sept 25, 2010.  Total Market 1 Sales: $10,000 - Last Sell Date: Feb 22, 2009. Total Market 2 Sales: $22,000 - Last Sell Date: Sept 25, 2010.

Customer 8910: No purchase record. 

I have a column for the date when a sale was made, sales dollars for both market segments, and a sale column for each market segment. I also have a column for customers. I think I have all the data there, I just need to figure out how to manipulate it to get my desired result.

Please help! 

Thanks in advance!

[amitchandak · Accepted solution]

There can be multiple ways to do this. One of the ways I can think of summarizes and union

Union data from Customer data and sales data

Add up = union(SUMMARIZE(Sales,Sales[Customer],"Col1",sum(Sales[Sales]),"Col2",Max(Sales[Date]),"Countfor dim",0),SUMMARIZE(Customer ,Customer[Customer],"Col1",0,"Col2",blank(),"Countfor Dim",COUNTROWS(Customer)) )

You Now on this table you can do the calculation to get when the last sales done , Does customer have sales etc.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

[Anonymous]

That worked! Thank you!