Power Pivot - marking the latest date based on duplicate values

[leemubai]

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]

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

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

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

 

[sandy666]

I need a solution in Power Pivot.

sure, have a nice day

 

[leemubai]

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]

Dear all, any idea?

 

[citizenbh]

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]

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]

OK, try this:

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

 

[leemubai]

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