Get sales value from the latest version of a project

[Enric21]

Hi legends, 

I hope somebody can help me. 

I have been struggling with getting the amount of sales of the latest version of certains projects. I have a table whith diferent projects an those projects have diferent versions (going from 0 to 5) and I would like to make a new table filtering only the amount of sales refering at the latests version of each project. 

Here you have an example table:

Project ID	Version	Sales
1	0	10
1	0	20
1	1	100
1	2	30
1	2	40
2	0	15
2	0	70
2	1	15
2	1	20
2	1	15
3	0	20
3	1	50
3	2	30

And here what I would like to get: 

Project ID	Version	Sales
1	2	70
2	1	50
3	2	30

As you can see, project 1 have three versions (0, 1 and 2) and I am only interested in the last one (version 2).

Thank you very much.

[v-jingzhang · Accepted solution]

Hi @Enric21 

If you want a DAX method, you can first create a new table with below code:

Table 2 = SUMMARIZE('Table','Table'[Project ID],"Version",MAX('Table'[Version]))

Then add a new column in this table 

Sales = SUMX(FILTER('Table','Table'[Project ID]='Table 2'[Project ID]&&'Table'[Version]='Table 2'[Version]),'Table'[Sales])

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Enric21]

Hi @v-jingzhang 

Thanks again for your accurate response. 

I have now another challenge that I cannot solve regarding the same table. 

The point is, my table in addition to projects it also has chapters, so each project has many chapters. The created table following your instructions gave me the total amount of the latest version of each project as I asked for, but now I would like to know how to make that table (or another one) taking into account those chapters as well. So as an example the original table is like: 

Project ID	Version	Chapter	Sales
1	0	01.01	10
1	0	01.01	15
1	1	01.01	15
1	1	01.02	20
1	1	01.02	15
1	2	01.01	30
1	2	01.01	30
1	2	01.02	50
2	0	01.01	20
2	0	01.02	20
2	1	01.01	100
2	1	01.02	40
2	1	01.02	70

And the result I would like to get would be like: 

Project ID	Version	Chapter	Sales
1	2	01.01	60
1	2	01.02	50
2	1	01.01	100
2	1	01.02	110

Thank you very much in advance

[v-jingzhang]

Hi @Enric21 

Sorry for the late reply. I transform the table to get this result in Power Query editor. Download the attachment to see the transformation steps there. 

If you just want to achieve this expected output in a table visual in report and don't want to transform the original table structure, we can also create measures to do that. Let me know what you prefer.

Regards,

Jing

[Enric21]

Many thanks Jing, 

I replied your transformation steps and works perfectly. 

Regards, 

Enric.

[Enric21]

Hi @v-jingzhang 

Thank you very much for your response. This is the solution I was looking for.