Forum Discussion
Second latest date with data
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
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!!
2 Replies
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!!
Thanks a lot VahidDM ! Your solution worked for my problem.