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 sample of what I am trying to achieve:

IDMonthIsMax
AJan-21  1
AJan-21  1
ADec-20 
ADec-20 
ADec-20 
BFeb-21  1
BFeb-21  1
BDec-20 
BDec-20 
CNov-20 
CDec-20  1
CDec-20  1
CDec-20  1

 

Any help is appreciated.

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

7 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi gclements 

    What's the logic behind which month is the Max?

    For A you have flagged Jan, but for C you have flagged Dec?

    Regards

    Phil

    • gclements's avatar
      gclements
      Helper II
      5 years ago

      It is to identify the maxium month value for each group.  So for group A the maxium month is Jan-21, therefore all rows for Jan-21 are flagged, for group B it is Feb-21...etc.

      • gclements's avatar
        gclements
        Helper II
        5 years ago

        Just to be a bit clearer, I am trying to calculate the 'IsMax' column.

  • timg's avatar
    timg
    Solution Sage
    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

    • gclements's avatar
      gclements
      Helper II
      5 years ago

      Thanks for the solution.  I have now realised that I have asked the wrong question as this will not resolve what I am trying to do.  Nevertheless this is the answer to my question so I will mark it as so and ask another question in another post.