Find latest value (text + decimals) by date/timestamp

[Anonymous]

Hi! I have a bunch of records (events) coming from the Firebase. Each contains a device operation system version (e.g. Android 9, Android 11). So, I need to create a measure to show the latest OS version for each user. Trying to simply maximize one gives an incorrect result, 'cos "Android 9" > "Android 10". I also tried to handle this via the LASTNOBLANK command, but it doesn't work.

I could use the separate table with the information needed (via SQL), but it's crucial to maintain the report flexible with least aggregated data

Best regards

[speedramps]

Hi VKorikhin

Please conisder this solution and smash the thumbs up button.

In Power Query ...

• right click on the "Query OS version" field > By Non Digit to Digit  all call the column "OS"
  
  
• right click on the "Query OS version" field > By Digit to Digit  all call the column "Version"
  
  
• Change the "Version" data typoe to numeric

The rest should be straight forward

[jppv20]

@Anonymous Could you provide some example data?

[Anonymous]

[v-easonf-msft]

Hi, @Anonymous 

Try the following measures:

os_num = 
VALUE ( MID ( MAX ( 'Table'[os] ), 9, 3 ) )

Max(os) = 
CALCULATE (
    MAX ( 'Table'[os] ),
    FILTER (
        'Table',
        'Table'[name] = MAX ( 'Table'[name] )
            && 'Table'[device] = MAX ( 'Table'[device] )
            && [os_num]
                = MAXX ( ALLEXCEPT ( 'Table', 'Table'[name], 'Table'[device] ), [os_num] )
    )
)

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jppv20]

Hi @Anonymous ,

An option could be to create a calculated column with only the number for each version. In that case 10 > 9. You can then create a formula based on the max of this column.

Hope this helps!

Jori

If I answered your question, please mark it as a solution to help other members find it more quickly.

Connect on Linkedin

[Anonymous]

There're such versions as 7.1.1

It would be nice not to create additional columns. It's a lot of data

[speedramps]

Please consider this solution ....

https://community.powerbi.com/t5/Desktop/Sorting-Version-Number-with-multiple-decimal-points-formatt...