topic Re: Second latest date with data in DAX Commands and Tips

Second latest date with data

Anonymous — Mon, 11 Oct 2021 07:22:35 GMT

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

Re: Second latest date with data

VahidDM — Mon, 11 Oct 2021 12:46:24 GMT

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!!

Re: Second latest date with data

Anonymous — Wed, 13 Oct 2021 15:57:52 GMT

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