Second Last and Third Last values not showing all values

[Anonymous]

Hello,

I would like to build some measures that will show the Second Last and Third last values, so I can use them lately in a formula. I have two years of dat, 2013, 2014 with 4 readings in 2013 and 3 readings in 2014. I created the measures for first value and last value and they work fine. But when I calculated second last and thirdlast values when visualize, it doesn't show all values. Here is the table with all data

and the table when using the measures, with a slicer for year. As you can see is that for 2013, second and third last values doesn't show all values. Not sure why. Any help is much appreciated. Thank you.

Here are the measures I used. I would like to insert the .pbix file, but not sure how.

FirstValue =

    CALCULATE ( MIN ( 'Piping UT ex'[Value]), FIRSTDATE ( 'Piping UT ex'[Date] ) )

LastValue =

CALCULATE(MIN('Piping UT ex'[Value]), LASTDATE('Piping UT ex'[Date]))

LatestDate =

    MAX('Piping UT ex'[Date])

SecondLatestDate =
VAR __LatestDate = [LatestDate]
RETURN
CALCULATE (
MAX ( 'Piping UT ex'[Date] ),
KEEPFILTERS ( 'Piping UT ex'[Date] < __LatestDate )
)

SecondLatestValue =
VAR __SecondLatestDate = [SecondLatestDate]
RETURN
CALCULATE (
SELECTEDVALUE ( 'Piping UT ex'[Value] ),
KEEPFILTERS ( 'Piping UT ex'[Date] = __SecondLatestDate )
)

ThirdLatestDate =
VAR __SecondLatestDate = [SecondLatestDate]
RETURN
CALCULATE (
MAX ( 'Piping UT ex'[Date] ),
KEEPFILTERS ( 'Piping UT ex'[Date] < __SecondLatestDate )
)

ThirdLatestValue =
VAR __ThirdLatestDate = [ThirdLatestDate]
RETURN
CALCULATE (
SELECTEDVALUE ( 'Piping UT ex'[Value] ),
KEEPFILTERS ( 'Piping UT ex'[Date] = __ThirdLatestDate )
)

[v-easonf-msft]

Hi, @Anonymous 

No obvious problems were found in the formula. I may need more details.

You can upload your sample file to cloud storage and share the public link here.

Best Regards,
Community Support Team _ Eason

[Anonymous · Accepted solution]

Hi @v-easonf-msft ,thank you for looking into it. I figured that was the case so I digged into the dataset example and I found multiple values for same dates, I cleaned it and it works. Lesson learned. Much appreciate your reply. Thank you