calculated collum lookup latest "workers" by "customer ID"

[Anonymous]

can someone please help me with this table:

i need a calculated collum that gives me the most recent "workers" for each "customer ID"

can someone help me please?

thanks

[Greg_Deckler]

Should be along the lines of:

Calculated collumn =
  MAXX(FILTER('Table',[Customer ID] = EARLIER([Customer ID]),[Workers])

[AlB]

Hi @Anonymous 

Do you want to do this in Power Query or DAX?  Can you paste the table in tabular-text format rather than in a screen cap, so thadt it can be copied?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[Anonymous]

Here it is 🙂

I am trying to do in power query but, if you don´t mind, pleaso also include the solution in DAX.

Entity	Customer ID	Order ID	Order Data	Workers	Calculated collum
A	12345	150333	2015-01-01	16
A	12345	1517	2010-01-01	20
B	34532	6812	2012-01-01	9
A	12345	12554	2014-01-01	40
B	34532	6812	2019-01-01	87

[AlB]

Hi @Anonymous 

You can copy this in a blank query and see the steps, the take from there what you are interested in. I've built an intermediate column with the latest date per Customer ID. It is not strictly necessary but it helps to make the code more readable

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fcxLCoAwDATQu2RdId9+lnqNkvtfw9RCFgpCYIbwmDnhhALEorbSUESiMJIdSHHrW8HLG1LbDJMxPuyKGkg4snbizTjZ+I6xmW6myfRvbSTrDdxv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Entity = _t, #"Customer ID" = _t, #"Order ID" = _t, #"Order Data" = _t, Workers = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Entity", type text}, {"Customer ID", Int64.Type}, {"Order ID", Int64.Type}, {"Order Data", type date}, {"Workers", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Latest_date", each List.Max(Table.SelectRows(#"Changed Type",(inner)=>[Customer ID]=inner[Customer ID])[Order Data])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Calc_Column", each Table.SelectRows(#"Added Custom",(inner)=>([Customer ID]=inner[Customer ID] and inner[Order Data]=inner[Latest_date]))[Workers]{0}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Latest_date"})
in
    #"Removed Columns"

 Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[AlB]

@Anonymous 

And the calculated column in DAX  (simpler, DAX is better equipped for this type of operations):

Calc_Column = 
VAR lates_date_ = CALCULATE(MAX(Table1[Order Data]); ALLEXCEPT(Table1;Table1[Customer ID]))
RETURN
CALCULATE(DISTINCT(Table1[Workers]);ALLEXCEPT(Table1;Table1[Customer ID]);Table1[Order Data] = lates_date_)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[Anonymous]

@AlB 

when i paste the DAX function that you have supplied in the new custom collum created, appears an error message "A table of multiple values was supplied where a single value was expected"

can you help me?

thanks

[AlB · Accepted solution]

Hi @Anonymous 

Are you using the code in a calculated column (and not a measure)? If so, that error means there is more than one row with the same Customer ID and the latest date, with different numbers in Workers. You'll need to decide what to do in that case. If you for instance want the maximum, just change the DISTINCT( ) for MAX()

Calc_Column = 
VAR lates_date_ = CALCULATE(MAX(Table1[Order Data]); ALLEXCEPT(Table1;Table1[Customer ID]))
RETURN
CALCULATE(MAX(Table1[Workers]);ALLEXCEPT(Table1;Table1[Customer ID]);Table1[Order Data] = lates_date_)

 Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[Anonymous]

Many thanks @AlB !!!

It worked perfect 🙂