Create New Table with only the last sale per product and per customer

[thibbos]

Hi there,

I have a table that contains lots of rows per customer, per product, per date and the sale of it.

Every customer could order the same product every month, so basically I have twelve different sales date per customer/per product.

Now I wanted to calculated the sum of sales for all the MAX date for that product and that customer. In order to do that I created a new table SUMMARIZING per product/customer/latest date ... Now the issue I have is that for the sales, it only seems to give me a sum of all sales.

I want a new table so that I can add up the total for all products and customers (that's what I needed). I hgave tried with a measure but somehow it doesn't give me actual results

Thanks a lot everyone

see example:

ACTUAL TABLE
Customer	Product	Date	Sale
A	1	Jan-20	100
A	1	Sep-20	200
A	2	Mar-20	300
A	2	Dec-20	400
DESIRED TABLE
Customer	Product	Date	Sale
A	1	Sep-20	200
A	2	Dec-20	400

[thibbos]

Hi @v-deddai1-msft  , thanks for the proposition. Sorry for not answering sooner but I moved to another issue.

I will look at it in time and mark it as a solution when I have the chance to check it out.

BR

Thibault

[v-deddai1-msft · Accepted solution]

Hi @thibbos ,

You can use the following measure:

Table1 =
ADDCOLUMNS (
    SUMMARIZE (
        'Table',
        'Table'[Customer],
        'Table'[Product],
        "Date", MAX ( 'Table'[Date] )
    ),
    "sales",
        CALCULATE (
            SUM ( 'Table'[Sale] ),
            FILTER ( 'Table', 'Table'[Date] = EARLIER ( [Date] ) )
        )
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai