Remove duplicates getting the last date and doing a sum of another ones

[PwrBI01]

Hi guys, 

I have the next table in Power BI:

I want to create a new table removing duplicates in the column 'IDEXPEDIENTE' with the next characteristics:

Column 'FECHAFACTURA'. Put the last date of the duplicates values

Column 'TOTALBRUTO'. Calculate the sum of 'TOTALBRUTO' values that have the same 'IDEXPEDIENTE'

Column 'TIPOIVA'. Put the number of the last date

Column 'CUOTAIVA'. Calculate the sum of 'CUOTAIVA' values that have the same 'IDEXPEDIENTE'

Column 'TOTALFACTURA'. Calculate the sum of 'TOTALFACTURA' values that have the same 'IDEXPEDIENTE'

Column 'IDFACTURACION'. Put the value of the last date

I should obtain a table similar to the next one:

Thanks in advance.

Regards.

[mhossain · Accepted solution]

Hello @PwrBI01 

Assuming your table name is "Table", try below dax to create new table.

1). Dax method

Go to Table tool and create DAX table, and use below DAX

Table 2 = SUMMARIZE('Table',

'Table'[IDEXPEDIENTE],

"FECHAFACTURA",LASTDATE('Table'[FECHAFACTURA]),

"TOTALBRUTO",SUM('Table'[TOTALBRUTO]),

"TIPOIVA",LASTNONBLANKVALUE('Table'[TIPOIVA],VALUES('Table'[TIPOIVA])),

"CUOTAIVA",SUM('Table'[CUOTAIVA]),

"TOTALFACTURA",SUM('Table'[TOTALFACTURA]),

"IDFACTURACION",LASTNONBLANKVALUE('Table'[IDFACTURACION],VALUES('Table'[IDFACTURACION]))

)

2).

In PowerQuery you can create this easily using Group BY.

Please let me know if this solves and this is what you were looking for.

[PwrBI01]

Thank you very much, option 1 is just what I need.