Showing data with most recent date based on two columns

[Anonymous]

Hi all,

I would like to be able to show all companies, per database the version they are running on.

So in other words see company 'B', instead of seeing the 17 rows as shown in excel, in Power BI I would like to see just 3 rows of of the 3 different databases with the most recent version they are working on. In other words using the 'Date' column it should only show the most recent update. 

So in this case I would end up with 4 rows of data, Company A = 1 row and Company B = 3 rows with the latest date and its version. Please help!

Company	Database	Version	Date
A	NAV100Ont	4PSNL 10.00-011-28	6-2-2018
A	NAV100Ont	4PSNL 10.00-011-28	6-2-2018
A	NAV100Ont	4PSNL 10.0-011-29	15-2-2018
A	NAV100Ont	4PSNL 10.0-011-29	15-2-2018
A	NAV100Ont	4PSNL 10.0-013-13	7-3-2018
A	NAV100Ont	4PSNL 10.0-013-14	9-4-2018
B	NAV100Test	4PSNL 10.00-012-10	24-8-2017
B	NAV100Test	4PSNL 10.00-012-12	6-9-2017
B	NAV100Prod	4PSNL 10.00-012-12	11-9-2017
B	NAV100Test	4PSNL 10.00-012-14	15-9-2017
B	NAV100Prod	4PSNL 10.00-012-14	20-9-2017
B	NAV100Test	4PSNL 10.00-012-16	2-10-2017
B	NAV100Prod	4PSNL 10.00-012-16	9-10-2017
B	NAV100Test	4PSNL 10.00-012-22	24-11-2017
B	NAV100Prod	4PSNL 10.00-012-22	24-11-2017
B	NAV100Test	4PSNL 10.00-012-25	12-1-2018
B	NAV100Test	4PSNL 10.00-013-06	15-1-2018
B	NAV100Prod	4PSNL 10.00-012-25	22-1-2018
B	NAV100DSG	4PSNL 10.00-014-00	27-1-2018
B	NAV100DSG	4PSNL 10.00-014-00	13-3-2018
B	NAV100DSG	4PSNL 10.00-014-02	13-3-2018
B	NAV100Prod	4PSNL 10.00-014-03	25-4-2018
B	NAV100DSG	4PSNL 10.00-014-07	22-6-2018

[az38]

Hi @Anonymous 

or try new table

Table = summarize(Table1;Table1[Company];Table1[Database];"Last Version";MAX(Table1[Version]))

it will be correct if versions order are sorted by its names

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin

[Anonymous · Accepted solution]

@Anonymous Please create following measure to get the latest update record.

IsLatest = 
VAR _latestDate = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Company],'Table'[Database]))
RETURN IF(MAX('Table'[Date])=_latestDate,TRUE(),FALSE())

 In case you want a separate table then create a measure in original table and go to modeling tab and create a table with below formula

Table 2 = FILTER('Table',[Measure]=TRUE())

[Anonymous]

@Anonymous 

This doesn't seem to look at the separate databases and as you can see doesn't fully work as e.g. with the second customer there is no date with 'True'. I would need 3 True's for the three different databases with the latest date. Thanks for thinking this through!

See here the result 

[Anonymous]

@Anonymous can you please confirm if this is the expected result?

[Anonymous]

@Anonymous That is indeed what I need. Perhaps I'm doing something wrong, my measure is:

IsLatest =

VAR _latestDate = CALCULATE(MAX('Sharepoint Changelog'[Date]);ALLEXCEPT('Sharepoint Changelog';'Sharepoint Changelog'[Company];'Sharepoint Changelog'[Database]))

RETURN IF(MAX('Sharepoint Changelog'[Date])=_latestDate;TRUE();FALSE())

Where the Sharepoint Changelog is the source. The first customer with one database is done correctly, second company I get no True's and third company is incorrect... See here my result

[Anonymous]

@Anonymous Can you please check if the data type of Sharepoint Changelog'[Date] column is date or not? Data type should be date

[Anonymous]

@Anonymous 

See here my result 

It was indeed data type text, so I've changed that to 'Date' and it has made some aspects true, but for the second customer does show one 'True' now but I need three for the three differen database types with the most recent date. So still shows incorrect True statements... Perhaps I'm still doing something wrong. Thanks for looking into it.

[az38]

Hi @Anonymous didnt solution with summarize function satisfy you?

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin

[Anonymous]

@az38  See here the result 

This doesn't correspond, it shows the wrong databases with the wrong companies...