Forum Discussion

gclements's avatar
gclements
Helper II
5 years ago
Solved

Calculated Column to Identify Rows Associated with Max Month

Hi,   I am tring to create a calculated column called 'IsMax' which will flag the rows from the max month per ID.  There may be more than one row per ID that relates to the max month. Below is a s...
Need Help
  • timg's avatar
    timg
    5 years ago

    Hi gclements,

     

    Could you check if this calculated column does the trick for you? For every Month record it will check whether the value is equal to the max month value of the associated ID. 

    MaxDatePerID =
IF (
    TestTable[Month]
        = CALCULATE (
            MAX ( TestTable[Month] ),
            FILTER ( TestTable, TestTable[ID] = EARLIER ( TestTable[ID] ) )
        ),
    1,
    0
)

     

     

    Hope it helps!

     

    Regards,

     

    Tim

PhilipTreacy's avatar
PhilipTreacy
Super User
5 years ago

gclements 

Your initial post had Dec21 in row 3 but I see that has now been corrected.  So the logic is clear now.  having Jan21 as the max didn't make sense with Dec21 in there.

Phil

gclements's avatar
gclements
Helper II
5 years ago

Yes sorry, I noticed there was a slight mistake in one of the months.