Forum Discussion

DavidWaters's avatar
DavidWaters
Icon for Helper I rankHelper I
4 years ago
Solved

Flag the date one before the maximum for each reference in a table

Hi,

 

I have a table of references which have 10 rows per reference with different dates.  I needed to flag "1" in anew column for each maximum date within the 10 rows for each reference.  I did this using below:

 

Max Flag = if('Table'[DATE] =maxx(filter('Table','Table'[Reference] =EARLIER('Table'[Reference])),'Table'[DATE]), 1,0)
 
This gives me the 1 and 0 flagger per below example
 
But now I also need to flag a "2" in a new column, where this is the next latest date before the Max date I am already flagging.
 
Can't quite figure this out - can anyone help please?
 
Ref1 01/02/2021 0 0
Ref1 02/02/2021 0 2
Ref1 03/02/2021 1 0
Ref2 04/02/2021 0 0
Ref2 05/02/2021 0 2
Ref2 06/02/2021 1 0
Ref3 07/02/2021 0 0
Ref3 08/02/2021 0 2
Ref3 09/02/2021 1 0
 
But now I also need to flag a "2" in a new column, where this is the next latest date before the Max date I am already flagging.
 
Can't quite figure this out - can anyone help please?
 
Need Help
  • tackytechtom's avatar
    tackytechtom
    4 years ago

    Hi DavidWaters ,

    I adapted accordingly:

    Max Flag = 
VAR _maxDate = CALCULATE ( MAX ('Table'[Date] ), ALLEXCEPT ('Table','Table'[Reference] ) )

RETURN
IF ( 'Table'[Date] = _maxDate, 1, 0)
    Second Max Flag = 
VAR _maxDate = CALCULATE ( MAX ('Table'[Date] ), ALLEXCEPT ('Table','Table'[Reference] ) )
VAR _secondMaxDate = CALCULATE ( MAX ('Table'[Date] ), ALLEXCEPT( 'Table','Table'[Reference] ), 'Table'[Date] < _maxDate )

RETURN
 IF ( 'Table'[Date]  = _secondMaxDate, 2, 0 )

     

    Does it work now?

    /Tom

    https://www.tackytech.blog

    https://www.instagram.com/tackytechtom

     

     

     

     

     

6 Replies

  • tackytechtom's avatar
    tackytechtom
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    Hi DavidWaters ,

     

    I got this result....

     

    ... by using the following code to create two calculated columns:

    Max Flag = 
VAR _maxDate = MAX ('Table'[Date] )

RETURN
IF ( 'Table'[Date] = _maxDate, 1, 0)
    Second Max Flag = 
VAR _maxDate = CALCULATE ( MAX ('Table'[Date] ), ALLSELECTED( 'Table' ) )
VAR _secondMaxDate = CALCULATE ( MAX ('Table'[Date] ),ALLSELECTED( 'Table' ), 'Table'[Date] < _maxDate )

RETURN
 IF ( 'Table'[Date]  = _secondMaxDate, 2, 0 )

     

    Did that solve your issue? 🙂

     

    /Tom

    https://www.tackytech.blog

    https://www.instagram.com/tackytechtom

     

    • DavidWaters's avatar
      DavidWaters
      Icon for Helper I rankHelper I
      4 years ago

      Hi tackytechtom 

       

      Sorry, it needs to work per reference as below - I didn't add enough example data.  It has to work per reference, I think your solution does not take this into account?  My mistake for not providing a full example.  Can you adapt your solution to make it work against each reference please?

       


      Ref1 01/02/2021 0 0
      Ref1 02/02/2021 0 2
      Ref1 03/02/2021 1 0
      Ref2 04/02/2021 0 0
      Ref2 05/02/2021 0 2
      Ref2 06/02/2021 1 0
      Ref3 07/02/2021 0 0
      Ref3 08/02/2021 0 2
      Ref3 09/02/2021 1 0

       

       

      • tackytechtom's avatar
        tackytechtom
        Icon for Most Valuable Professional rankMost Valuable Professional
        4 years ago

        Hi DavidWaters ,

        I adapted accordingly:

        Max Flag = 
VAR _maxDate = CALCULATE ( MAX ('Table'[Date] ), ALLEXCEPT ('Table','Table'[Reference] ) )

RETURN
IF ( 'Table'[Date] = _maxDate, 1, 0)
        Second Max Flag = 
VAR _maxDate = CALCULATE ( MAX ('Table'[Date] ), ALLEXCEPT ('Table','Table'[Reference] ) )
VAR _secondMaxDate = CALCULATE ( MAX ('Table'[Date] ), ALLEXCEPT( 'Table','Table'[Reference] ), 'Table'[Date] < _maxDate )

RETURN
 IF ( 'Table'[Date]  = _secondMaxDate, 2, 0 )

         

        Does it work now?

        /Tom

        https://www.tackytech.blog

        https://www.instagram.com/tackytechtom

         

         

         

         

         

    • DavidWaters's avatar
      DavidWaters
      Icon for Helper I rankHelper I
      4 years ago

      I used Maxx to get per reference level, but can't figure out how to flag the second max date!