Forum Discussion
Solved
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...
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 🙂
I 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 |
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 🙂