how to calculate sum of last date sales by customer if we have different last date by customers

[Anonymous]

This is 3rd time , I am asking same help" to resolve the issue",can you help me , I did my best but not working ... how to calculate sum of last date sales by customer if we have diffrent last date by customers

sample table

Customers Id    Date          Amount

KSF-001            01-01-20   20000

KSF-002            02-01-20   50000

KSF-001            01-31-20   15000

KSF-003           05-23-20    5000

KSF-001           02-11-20    25000

KSF-002           03-01-20   60000

KSF-002           04-01-20    25000

Total 200000

Need solution like this

Customers Id          Date             Amount

KSF-003                  05-23-20       5000

KSF-001                  02-11-20      25000

KSF-002                 04-01-20       25000

Total 55000 Regards Faslin 

[mahoneypat · Accepted solution]

Please make these two measures (replacing with your table and column name).  Use the 2nd one in your table visual with Customer Id column, to get your desired result.

Last Date Amount =
CALCULATE (
SUM ( Data[Amount] ),
TOPN ( 1, VALUES ( Data[Date] ), Data[Date], DESC )
)

Sum of Last Dates Each Customer =
SUMX ( VALUES ( Data[Customer Id] ), [Last Date Amount] )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

superb...Thank you soo much , much appriciated 

Regards

Faslin 

[Smauro]

Hi @Anonymous,

Seeing that you ask for a power query solution to that, what about grouping?

You could try this:

#"Grouped Rows" = Table.Group(PreviousStep, {"Customers Id"}, {{"Date", each List.Max([Date]), type date}, {"Amount", each let d = List.Max([Date]) in List.Sum(Table.SelectRows(_, each [Date] = d)[Amount]), type number}})

Cheers,

smauro

[MattAllington]

DAX problems are all the same - they are filtering problems.  I would approach this as follows

1. Create a star schema (add a customer table and a date table, join 1 to many to your data table)

2. Create a visual with customer id from the customer table, date from the date table 

3. write the measures

last sale date = max(calendar[date])

sales on last date =calculate(sumx(customerTable,calculate(sum(fact[amount]))),Filter(calendar,calendar[date]=[last sale date])

or possibly as follows (you would need to test them)

=sumx(customerTable,calculate(sum(fact[amount]),Filter(calendar,calendar[date]=[last sale date])))