Second latest date with data

[Anonymous]

Hi, 

I have a measure that shows data from the latest possible day and it works as expected. Somehow I fail building a measure for the second latest date because it is always showing data for the next day after latest and new data is not coming in daily. The timestamp value has a relation with the Date table.

Measure for latest works as expected:

Measure 1 =

var _latestday = CALCULATE(MAX('Date'[Date]))

RETURN
CALCULATE(SUM('Table'[count]), 'Table'[timestamp] = _latestday)

Measure for second latest works is returning second latest date even if count is blank.

Measure 2 =
var _secondlatestday = CALCULATE(MAX('Date'[Date]))-1

RETURN
CALCULATE(SUM('Table'[count]), 'Table'[timestamp] = _secondlatestday)

So today Measure 1 is returning 100 and Measure 2 blank when 140 is expeted. On Friday 8th of October Measure 1 was returning 140 and Measure 2 was returning 80. On Friday I thought my measure was working fine but today I realized something needs to be fixed.

timestamp	count
10/10/21	100
8/10/21	140
7/10/21	80
5/10/21	9
3/10/21	-2

Thanks for your help in advance!

Julia

[Anonymous]

Thanks a lot @VahidDM ! Your solution worked for my problem.

[VahidDM · Accepted solution]

Hi @Anonymous 

try this:

VAR _2nd =
    CALCULATE (
        MAX ( 'Table'[timestamp] ),
        FILTER ( ALL ( 'Table' ), 'Table'[timestamp] < MAX ( 'Table'[timestamp] ) )
    )
RETURN
    CALCULATE ( SUM ( 'Table'[count] ), 'Table'[timestamp] = _2nd )

output:

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

Appreciate your Kudos!!