Forum Discussion

heygowtam's avatar
heygowtam
Icon for Helper II rankHelper II
3 years ago

Get a Second maximum value From COlumn

I have a Table of three Columns 

DateSeasonBottles
14/12/20201916
12/08/20201921.4
11/06/20212064
11/08/20212151
5/11/20212114
10/10/20222215.3



1) Sum the Bottle quantity by Season number then 

 

2) I need to Find the Difference From

---Calculate(SUM[Bottles]  WHERE  season = MAX[season]  - SUM[Bottles ] WHERE season =  SECONDMAX [season])

My question is

If i want Selelct Maximum Value - Max[season],

How can i select  second MAX value 

Need Help

3 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    3 years ago

    Hi heygowtam 

     

    Use this DAX expression to find the Second Max date:

    Second Max = 
Var _Max = MAX( 'Table'[Date] )
return
CALCULATE(MAX( 'Table'[Date] ),REMOVEFILTERS('Table'),'Table'[Date]<_Max)

     

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

    Appreciate your Kudos!! 

    LinkedIn | Twitter | Blog | YouTube 

    • heygowtam's avatar
      heygowtam
      Icon for Helper II rankHelper II
      3 years ago

      VahidDM 
      Thanks for your Reply, 

      I need to pick second highest Season value, 
      then subtract from first maximum [Season ] Value,