Forum Discussion
Latest Value
Hello,
I want to create a measure or table that shows the latest value. I have a table that looks like this:
| Device_SerialNumber | Date | Software_Version |
| 12345 | 1/1/2022 | 2.3 |
| 12345 | 1/2/2022 | 2.3 |
| 12345 | 1/3/2022 | 2.3.1 |
| 12345 | 1/4/2022 | 2.3.1 |
| 12345 | 1/5/2022 | 2.3.1 |
| 12345 | 1/6/2022 | 2.2 |
I wanted to use Latest Software Version = MAX(Software_Version) , but that would result in the greatest value, rather than the last according to the date column. What can I use?
mariajuliao try this:
Measure = VAR _max_date = CALCULATE(MAX('Table'[Date]), REMOVEFILTERS('Table'[Date])) VAR _result = CALCULATE( CONCATENATEX('Table','Table'[Software_Version], ", "), 'Table'[Date] = _max_date ) RETURN _result
In case you have a date table then it will be like this:Measure = VAR _max_date = CALCULATE(MAX('Table'[Date]), REMOVEFILTERS('Date') VAR _result = CALCULATE( CONCATENATEX('Table','Table'[Software_Version], ", "), 'Table'[Date] = _max_date ) RETURN _result
Let me know if that was it or we need to go deeper 🙂
4 Replies
mariajuliao
Write this:Measure = VAR _max_date = CALCULATE(MAX('Table'[Date]), REMOVEFILTERS()) VAR _result = CALCULATE( CONCATENATEX('Table','Table'[Software_Version], ", "), 'Table'[Date] = _max_date ) RETURN _result
Here is a link to download the file with the solution:
Latest Value 2022-07-28.pbixI believe this formula would work if I was only looking for the latest software version of all. I want to be able to see it per device serial number, device type, and other columns in the table.
The output should be something like this:
Device_SerialNumber Latest_Software_Version 12345 2.2 12346 2.3.1 12347 2.3 And also be able to do a count like this:
Latest_Software_Version Count_of_Device_Serial_Number_Distinct 2.2 10 2.3 11 3.0 3 3.1 7 3.1.1 25
