Forum Discussion

M_SBS_6's avatar
M_SBS_6
Helper V
2 years ago
Solved

Second latest Date

Hi,

I have the following measure which pulls the value based on the max date. 

 

Measure= Calculate (sum( app[val], filter(app[date] = MAX (app[date])))

 

This brings back 01/12/2023.

 

The previous date I have is 29/11/2023. How can I tailor my existing measure to return the second MAX date please and output 200 as per example below?

 

Date.                Val

01/12/2023.     500

29/11/2023.     200

Need Help
  • Ahmedx's avatar
    Ahmedx
    2 years ago

    Try this

    Measure = VAR _Datesecond = MINX(TOP(2,ALL('app'[Date]),'app'[Date],DESC),'table'[Date])
RETURN
CALCULATE(sum(app[val]),FILTER(ALL('app'),'app'[Date]=_Datesecond))

3 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    Try this

    Measure = VAR _Datesecond = MINX(TOP(2,ALL('app'[Date]),'app'[Date],DESC),'table'[Date])
RETURN
CALCULATE(sum(app[val]),FILTER(ALL('app'),'app'[Date]=_Datesecond))
  • miTutorials's avatar
    miTutorials
    Super User
    2 years ago

    Please try the below measure.

     

    Second_Max_Date  = 
VAR MAX_ = CALCULATE(MAX('Table (4)'[Date]))
Return CALCULATE(MAX('Table (4)'[Val]),
 'Table (4)'[Date] < MAX_)