Latest Value

[mariajuliao]

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?

[SpartaBI]

@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.pbix

      

Showcase Report – Contoso By SpartaBI

[mariajuliao]

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

[mariajuliao]

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

[SpartaBI · Accepted solution]

@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 🙂

      

Showcase Report – Contoso By SpartaBI