Flag on the second max month

[BigKev]

Hi All,

Does anyone know how I can create a flag to mark on the second Max month?

Below is my data set

Date	Flag
1/01/2021	0
1/02/2021	0
1/03/2021	0
1/04/2021	0
1/05/2021	1
1/06/2021	0

As you can see that 1/06/21 is my max month in this table, but I want to put a flag on the month before 1/05/21.

When 01/07/21 data is available, then the flag "1" will be moved to 01/06/21. 

Date	Flag
1/01/2021	0
1/02/2021	0
1/03/2021	0
1/04/2021	0
1/05/2021	0
1/06/2021	1
1/07/2021	0

Could someone please help me?

Thanks.

Cheers,

Kev

[v-rzhou-msft]

Hi @BigKev 

You can try my way to build a rank by var function and use if to show result.

Flag = 
VAR _RANK = RANKX('Table','Table'[Date],,DESC,Dense)
Return
IF(_RANK = 2,1,0)

Result:

Update the data:

Best Regards,

Rico Zhou

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

[CNENFRNL]

Seems other solutions aren't robust enough when a month contains more than one date.

Flag = 
VAR __max = MAX( 'Table'[Date] )
RETURN
    IF( ISEMPTY(
            FILTER(
                CALCULATETABLE(
                    PREVIOUSMONTH( 'Table'[Date] ),
                    'Table'[Date] = __max,
                    REMOVEFILTERS()
                ),
                'Table'[Date] = EARLIER( 'Table'[Date] )
            )
        ),
        0,1
    )

[Hariharan_R]

Hi Kev,

   Use the below formula. 

Flag =

VAR MaxMonth =

CALCULATE ( MAX ( 'Table'[Date] ), ALL ( 'Table'[Date] ) )

VAR PreMax =

CALCULATE (

MAX ( 'Table'[Date] ),

FILTER ( ALL ( 'Table' ), 'Table'[Date] < MaxMonth )

)

RETURN

IF ( MIN ( 'Table'[Date] ) = PreMax, 1, 0 )

Thanks

Hari 

Please mark answer if it resolves your problem

[Jihwan_Kim]

Hi, @BigKev 

Please check the below picture and the sample pbix file's link down below.

It is for creating a measure, not a column.

Second Max Flag Measure =

VAR newtable =

TOPN ( 2, ALL ( 'Table'[Date] ), CALCULATE ( MAX ( 'Table'[Date] ) ), DESC )

RETURN

IF( ISFILTERED('Table'[Date]),

IF ( MAX ( 'Table'[Date] ) = MINX ( newtable, 'Table'[Date] ), 1, 0 )

)

https://www.dropbox.com/s/5ga7njcyc4w47rh/bigkevv2.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[BigKev]

Hey Jihwan,

Do you know how I can achieve this on the calculated column instead of measures?

Cheers,

Kev

[Jihwan_Kim]

Hi, @BigKev 

Thank you for your feedback.

Please check the link down below.

Second Max Flag CC =
VAR newtable =
TOPN ( 2, 'Table', 'Table'[Date], DESC )
RETURN
IF ( 'Table'[Date] = MINX ( newtable, 'Table'[Date] ), 1, 0 )

https://www.dropbox.com/s/5ga7njcyc4w47rh/bigkevv2.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Hariharan_R · Accepted solution]

Hi Kev,

Use this in calculated column.

Flag =
IF (
'Table'[Date]
= CALCULATE (
MAX ( 'Table'[Date] ),
FILTER ( 'Table', 'Table'[Date] <> MAX ( 'Table'[Date] ) )
),
1,
0
)

Thanks

[BigKev]

Hey @Hariharan_R,

Thanks for the answer. Just wondering, instead of just flag the second max month, how can I flag the second max month and before?

This is what I want to achieve:

Date              Flag

1/01/2021       1

1/02/2021       1

1/03/2021       1

1/04/2021       1

1/05/2021       1

1/06/2021       0

[v-rzhou-msft]

Hi @BigKev 

Try this measure:

Flag = 
VAR _Max = MAX ( 'Table'[Date] ) RETURN IF ( 'Table'[Date] < _Max, 1, 0 )

Result is as below.

Best Regards,

Rico Zhou

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