How to summarize with SUMMARIZECOLUMNS the last 4 invoices of each client

[renan_arrieiro]

Hi guys,

I have a table with columns:

Shop	Customer	Date	Value
C1	137643	10/01/2019	6240
C1	137643	23/01/2019	1473,52
CD	12750	22/01/2019	1053,02
CD	133015	19/02/2019	598,78
C1	140696	12/02/2019	503,48
C1	140696	14/02/2019	446,65
C1	140696	24/01/2019	322,45
...	...	...	...

for each customer purchase.

I need a table summarized with the last 4 invoices of each client, example result:

Shop	Customer	Date	Value
C1	137643	10/01/2019	1.000,00
C1	137643	23/01/2019	1.500,00
C1	137643	16/12/2018	2.658,00
C1	137643	08/11/2018	2.036,00
CD	12750	05/02/2019	100,00
CD	12750	10/01/2019	233,00
CD	12750	07/11/2018	50,00
CD	12750	15/10/2018	88,00

I've been trying all morning.

Thanks for all help.

[v-cherch-msft · Accepted solution]

Hi @renan_arrieiro 

You may create a rank column for the table.Then create a table with SUMMARIZE Function to get the last 4 invoices of each client.

Rank = RANKX(FILTER(Table2,Table2[Customer]=EARLIER(Table2[Customer])),Table2[Date],,DESC)

New Table =
SUMMARIZE (
    FILTER ( Table2, Table2[Rank] <= 4 ),
    Table2[Shop],
    Table2[Customer],
    Table2[Date],
    Table2[Value]
)

Regards

[renan_arrieiro]

Thanks very much for your time @v-cherch-msft,

with some adjustments worked perfectly,

Rank column:

RANKX(
  FILTER(
    F_FATURA;
    F_FATURA[CLIENTE] = EARLIER(F_FATURA[CLIENTE]) && //CUSTOMER
    F_FATURA[CANAL]   = EARLIER(F_FATURA[CANAL]) //SHOP
  );
  F_FATURA[DATA_FATURAMENTO]; //DATE
  ;
  DESC;
  DENSE
)

new table:

F_ULTIMAS_FATURAS = 
SUMMARIZECOLUMNS(
    F_FATURA[CANAL]; //SHOP
    F_FATURA[CLIENTE]; //CUSTOMER
    F_FATURA[DATA_FATURAMENTO]; //DATE
    F_FATURA[RANK_DATA]; //RANK
    FILTER ( F_FATURA; F_FATURA[RANK_DATA] <= 4 ) //FILTER
)

Thanks thanks.

now I'm going to the next part