# Power Pivot - marking the latest date based on duplicate values

#### leemubai

##### New Member
Dear all,

I have a table with dublicate order numbers in a columnn and individual date in a another column.

I only want to count the order with the most recent date, so I want to mark the order with the most recent date with a "1" in a calculated column. How can I solve it in Power Pivot?

Any idea?

 Order Date Column1 123​ 12.02.2020​ 1​ 123​ 11.02.2020​ 0​ 525​ 01.01.2020​ 1​ 526​ 02.01.2020​ 1​ 527​ 08.01.2020​ 1​ 527​ 19.01.2019​ 0​

KR leemubai

#### sandy666

##### Well-known Member
maybe Power Query
without "mark" column
 Order Date Column1 Order Date 123 12/02/2020 1 123 12/02/2020 123 11/02/2020 0 525 01/01/2020 525 01/01/2020 1 526 02/01/2020 526 02/01/2020 1 527 08/01/2020 527 08/01/2020 1 527 19/01/2019 0

Code:
``````// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Type = Table.TransformColumnTypes(Source,{{"Order", type text}, {"Date", type date}}),
Group = Table.Group(Type, {"Order"}, {{"Date", each List.Max([Date]), type date}})
in
Group``````

#### leemubai

##### New Member
Thanks for your help but I need a solution in Power Pivot.

#### leemubai

##### New Member
I found this formula in Excel but the same forumula does not exist in Power Pivot. Mybe someone of the experts form here could help me to translate this formula in Power Pivot?

 Date Column1 Support 123 12.02.2020 1​ (AGGREGATE(14;6;B\$2:B\$99/(A\$2:A\$99=A2);1)=B2)*(A2>0) 123 11.02.2020 0​ 525 01.01.2020 1​ 526 02.01.2020 1​ 527 08.01.2020 1​ 527 19.01.2019 0​

#### leemubai

##### New Member
Dear all, any idea?

#### citizenbh

##### Board Regular
1. In PowerPivot make new Pivot Table in Report Layout/Tabular Form (Tab Design)
2. In Rows put all fields
3. For Column1 make fileter 1

#### leemubai

##### New Member
1. In PowerPivot make new Pivot Table in Report Layout/Tabular Form (Tab Design)
2. In Rows put all fields
3. For Column1 make fileter 1
Thanks! Sorry, my question was not really clear. Actually I wanted to know if there is a formula in Power Pivot where I could add it in custom column to get the result like my example,

 Order Date Column1 123 12.02.2020 1 123 11.02.2020 0 525 01.01.2020 1 526 02.01.2020 1 527 08.01.2020 1 527 19.01.2019 0

KR leemubai

#### citizenbh

##### Board Regular
OK, try this:

Column1
=IF(Table1[Date]=CALCULATE(MAX([Date]),FILTER(Table1,[Order]=EARLIER([Order]))),1,0)

#### leemubai

##### New Member
OK, try this:

Column1
=IF(Table1[Date]=CALCULATE(MAX([Date]),FILTER(Table1,[Order]=EARLIER([Order]))),1,0)
Thanks for your efforts but I get this error message:

"Too few arguments were passed to the FILTER function. The minimum argument count for the function is 2."

=IF('Table1'[Date]=CALCULATE(MAX('Table1'[Date]);Filter('Table1'[Order]=EARLIER('Table1'[Order])));1;0)

KR