Create a new table with the newest prices

[pawelj795]

Hi

I have a table like below, more complicated of course.

Product	Price	Create Date
A	5	10.05.2021
A	7	30.04.2021
A	9	20.04.2021
A	2	05.03.2021
A	4	06.01.2021
B	3,3	10.05.2021
B	2,6	30.04.2021
B	1,9	20.04.2021
B	1,2	05.03.2021
B	0,5	10.05.2021
C	5	30.04.2021
C	3	20.04.2021
C	2	05.03.2021
C	4	10.05.2021
C	9	30.04.2021
D	5

Now, I want to create another table, based on the above one, with the latest prices for each product.
(SUMMARIZE/SUMMARIZECOLUMNS function I think)
Please, keep in mind, if the date is blank, it should be kept too. (example - product D)

[Anonymous · Accepted solution]

Hi @pawelj795 ,

Create a column to get max date for each product.

Column = IF(ISBLANK('Table'[Create Date]),BLANK(),MAXX(ALLEXCEPT('Table','Table'[Product]),'Table'[Create Date]))

Then filter the table with date = max date and date = blank.

Table 2 = FILTER('Table','Table'[Create Date]='Table'[Column]||'Table'[Column]=BLANK())

 

Best Regards,

Jay

[Jihwan_Kim]

Hi, @pawelj795 

Please check the below.

I created it in two steps.

The newest price table =

  // creating a new table

GROUPBY (

'Table',

'Table'[Product],

"@latestdate", MAXX ( CURRENTGROUP (), 'Table'[Create Date] )

)

Latest Price CC =

// creating a new column

CALCULATE (

SUM ( 'Table'[Price] ),

FILTER (

'Table',

'Table'[Product] = 'The newest price table'[Table_Product]

&& 'Table'[Create Date] = 'The newest price table'[@latestdate]

)

)

https://www.dropbox.com/s/2qr8qwy8lrl2l13/pawelj.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[amitchandak]

@pawelj795 , Try a table like

addcolumns(summarize(Table, Table[Product], "_1", max(Table[Create Date])), "_2", calculate(Max(Table[Price]), filter(Table, Table[Create Date] =_1)))