Power Pivot - marking the latest date based on duplicate values

leemubai

New Member
Joined
Feb 11, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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?



OrderDateColumn1
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
maybe Power Query
without "mark" column
OrderDateColumn1OrderDate
12312/02/2020112312/02/2020
12311/02/2020052501/01/2020
52501/01/2020152602/01/2020
52602/01/2020152708/01/2020
52708/01/20201
52719/01/20190

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
 
Upvote 0
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?

DateColumn1Support
12312.02.2020
1​
(AGGREGATE(14;6;B$2:B$99/(A$2:A$99=A2);1)=B2)*(A2>0)
12311.02.2020
0​
52501.01.2020
1​
52602.01.2020
1​
52708.01.2020
1​
52719.01.2019
0​
 
Upvote 0
  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
 
Upvote 0
  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,

OrderDateColumn1
12312.02.20201
12311.02.20200
52501.01.20201
52602.01.20201
52708.01.20201
52719.01.20190

KR leemubai
 
Upvote 0
OK, try this:

Column1
=IF(Table1[Date]=CALCULATE(MAX([Date]),FILTER(Table1,[Order]=EARLIER([Order]))),1,0)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top