Forum Discussion

Helper 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 th...

Need Help

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

tackytechtom

Most 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