Latest and Second to Latest Value but not the Earliest Value

[Anonymous]

Hello,

I'm very new to Power BI. I have a table setup like this. I'm trying to determine the Latest Value for Dimensions 1 and 2 compared to the second most recent value. I've found potential solutions however all of them return any previous value to the MAX assessment date and I need to only return the second most recent. Any help is appreciated!

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sevenhills]

It is not clear the output you are expecting. Are u looking like this? gives both latest and second latest dates

-- Measure 1
Latest Assessment Date = Max(Table1[Assessment_Date])

-- Measure 2
Second Latest Assessment Date = Maxx( Filter ( All(Table1[Assessment_Date]), Table1[Assessment_Date] < Max(Table1[Assessment_Date]) ), Table1[Assessment_Date])

I agree with @TomMartens , it is not clear ... 

[TomMartens]

Hey @Anonymous ,

Regarding your question, I have to admit that I have my problems understanding your requirement.

Please consider to create a pbix file that contains sample data, but still reflects the data model (https://docs.microsoft.com/en-us/learn/modules/design-model-power-bi/).
You can use this to enter data directly in Power BI Desktop: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-enter-data-directly-into-desktop. Upload the pbix file to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.
Create columns that contain the expected results, use the sample data to explain how the result is derived from the sample data.

Regards,

Tom

[Anonymous]

Hi @TomMartens 

I will work on a pbix file in a bit. My goal is to determine which clients had a lower score per dimension comparing the most recent assessment and the assessment prior. I uploaded an excel file here: BI_Help

I need to find the client's most recent values in dimensions 1 and 2 based on the assessment date; then I need to calculate the difference between the Recent Value and the 2nd Most Recent value.

I've tried some solutions to return the value that is not from the MAX date however, the returned values include any value where the assessment date was before the most recent assessment date. Some clients have 4 or more assessments and I only need the most recent 2, not all.

Thanks!