Return Value based on highest number, comparing rows

[Anonymous]

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

[amitchandak · Accepted solution]

@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 · Accepted solution]

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.

[amitchandak · Accepted solution]

@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]

Awesome, that did the job!

Any chance I could do the same in Power Query? 

I'd love to hear it!