Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Return Value based on highest number, comparing rows

Dear reader,

 

A demo from what the data looks like:

ID                           Value       YearMonth

CustomerA             $50          202009

CustomerA             $50          202008

CustomerA             $50          202007

CustomerB             $21          202007 

CustomerB             $21          202004

 

What I would like to do is add a column that returns value 1, based on the latest month (highest number), matching the same customer, as such:

 

ID                           Value       YearMonth       Indicator

CustomerA             $50          202009               1

CustomerA             $50          202008               0

CustomerA             $50          202004               0

CustomerB             $21          202001               1

CustomerB             $21          201911               0

 

This way I can filter out duplicates in my report. Im trying to use the earlier() function, but do not know how to return on highest value. Please let me know if my  request is possible.

 

Kind regards,

Daniël

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , Try

    new column = if([YearMonth] = maxx(filter(table, [ID] = earlier([ID])),[YearMonth]),1,0)

    or

    new measure = if(max(Table[YearMonth]) = maxx(filter(allselected(table), [ID] = max([ID])),[YearMonth]),1,0)

  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a custom column with following m codes.

    let 
id=[ID],
maxyearmonth=List.Max(
    Table.SelectRows(#"Changed Type",each [ID]=id)[YearMonth]
)
in 
if [YearMonth]=maxyearmonth
then 1 else 0

     

     

    Best Regards

    Allan

     

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try

    new column = if([YearMonth] = maxx(filter(table, [ID] = earlier([ID])),[YearMonth]),1,0)

    or

    new measure = if(max(Table[YearMonth]) = maxx(filter(allselected(table), [ID] = max([ID])),[YearMonth]),1,0)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Awesome, that did the job!

       

      Any chance I could do the same in Power Query? 

       

      I'd love to hear it!

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create a custom column with following m codes.

    let 
id=[ID],
maxyearmonth=List.Max(
    Table.SelectRows(#"Changed Type",each [ID]=id)[YearMonth]
)
in 
if [YearMonth]=maxyearmonth
then 1 else 0

     

     

    Best Regards

    Allan

     

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