Select and compare the 2 most recent results in a category

[nicoleddss]

Hello,

I have a table that is structured, at the most basic level, like this (dummy data)

I have a report to allow the user to select an analyte (example: Glucose). From there, I would like it to present the two most resent lab results, list, compare and be able to say what the difference is. the result would look similar to these two examples:

and this:

(column names are long to help explain the expected values). Any idea how to return only the results I want?

Thanks for any help you can offer!

[burakkaragoz · Accepted solution]

Hi @nicoleddss ,

Looking at your data and desired output, you need measures that find the top 2 most recent records for the selected analyte and calculate the difference.

Here's what you need:

Most Recent Date:

Most Recent Date = 
CALCULATE(
    MAX(YourTable[Date]),
    ALLEXCEPT(YourTable, YourTable[Analyte])
)

Most Recent Amount:

Most Recent Amount = 
CALCULATE(
    MAX(YourTable[Amount]),
    YourTable[Date] = [Most Recent Date]
)

2nd Most Recent Date:

2nd Most Recent Date = 
VAR MostRecentDate = [Most Recent Date]
RETURN
CALCULATE(
    MAX(YourTable[Date]),
    YourTable[Date] < MostRecentDate,
    ALLEXCEPT(YourTable, YourTable[Analyte])
)

2nd Most Recent Amount:

2nd Most Recent Amount = 
CALCULATE(
    MAX(YourTable[Amount]),
    YourTable[Date] = [2nd Most Recent Date]
)

Difference:

Difference = [Most Recent Amount] - [2nd Most Recent Amount]

For your table visual: Create a disconnected table with the row labels:

RowLabels = DATATABLE("Row", STRING, {{"Most Recent"}, {"2nd Most Recent"}, {"Difference"}})

Then create a dynamic measure that returns the right value based on which row is selected:

Dynamic Value = 
SWITCH(
    SELECTEDVALUE(RowLabels[Row]),
    "Most Recent", [Most Recent Amount],
    "2nd Most Recent", [2nd Most Recent Amount], 
    "Difference", [Difference],
    BLANK()
)

Use RowLabels[Row] on rows and your Dynamic Value measure in the values section. This approach gives you exactly the layout shown in your screenshots.

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[v-venuppu]

Hi @nicoleddss ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @burakkaragoz @lbendlin for the prompt response.

Can you please post the solution here that worked out for you.This will be helpful for other community members who have similar problems to solve it faster.

[nicoleddss]

Hello,

I wanted to note that I accepted both solutions. One solution was a quick way to get what I needed at the moment. The second was more specific to a longer-term solution. I used one on each report. Thank you to both responders!

[lbendlin · Accepted solution]

The fastest way to do that is to use visual calculations

No need to limit yourself to the last two values.

[nicoleddss]

Yes. That was fast! lol. Thank you!😁

[burakkaragoz · Accepted solution]

Hi @nicoleddss ,

Looking at your data and desired output, you need measures that find the top 2 most recent records for the selected analyte and calculate the difference.

Here's what you need:

Most Recent Date:

Most Recent Date = 
CALCULATE(
    MAX(YourTable[Date]),
    ALLEXCEPT(YourTable, YourTable[Analyte])
)

Most Recent Amount:

Most Recent Amount = 
CALCULATE(
    MAX(YourTable[Amount]),
    YourTable[Date] = [Most Recent Date]
)

2nd Most Recent Date:

2nd Most Recent Date = 
VAR MostRecentDate = [Most Recent Date]
RETURN
CALCULATE(
    MAX(YourTable[Date]),
    YourTable[Date] < MostRecentDate,
    ALLEXCEPT(YourTable, YourTable[Analyte])
)

2nd Most Recent Amount:

2nd Most Recent Amount = 
CALCULATE(
    MAX(YourTable[Amount]),
    YourTable[Date] = [2nd Most Recent Date]
)

Difference:

Difference = [Most Recent Amount] - [2nd Most Recent Amount]

For your table visual: Create a disconnected table with the row labels:

RowLabels = DATATABLE("Row", STRING, {{"Most Recent"}, {"2nd Most Recent"}, {"Difference"}})

Then create a dynamic measure that returns the right value based on which row is selected:

Dynamic Value = 
SWITCH(
    SELECTEDVALUE(RowLabels[Row]),
    "Most Recent", [Most Recent Amount],
    "2nd Most Recent", [2nd Most Recent Amount], 
    "Difference", [Difference],
    BLANK()
)

Use RowLabels[Row] on rows and your Dynamic Value measure in the values section. This approach gives you exactly the layout shown in your screenshots.

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.
This response was assisted by AI for translation and formatting purposes.

[nicoleddss]

Thank you very kindly burakkaragoz! It looks like the right approach for sure. I didn't try it yet because the other solution will work right now. But I will save it and let you know. 

Preview