Power Pivot - marking the latest date based on duplicate values

leemubai

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

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,299
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
 

leemubai

New Member
Joined
Feb 11, 2020
Messages
7
Office Version
365
Platform
Windows
Thanks for your help but I need a solution in Power Pivot.
 

leemubai

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

citizenbh

Board Regular
Joined
Sep 19, 2013
Messages
132
  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
Joined
Feb 11, 2020
Messages
7
Office Version
365
Platform
Windows
  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
 

citizenbh

Board Regular
Joined
Sep 19, 2013
Messages
132
OK, try this:

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

leemubai

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

Forum statistics

Threads
1,085,787
Messages
5,385,881
Members
401,975
Latest member
OnPoint

Some videos you may like

This Week's Hot Topics

Top