DAX lookupValue with date filter

[Lars1]

Hi

How to lookup a value in a table and only take the latest value ?
I have this table:

And i would like to create a new table with this result:
The result in the new table should only be the latest/newest value on each "Code".

Can anyone help with a DAX solution to this ?

 

[Lars1]

Solved by grouping by Code

 

[Lars1]

Not solved

 

[alansidman]

Use Power Query for this result

Book2
	A	B	C	D	E	F	G
1	Code	StartDate	Kurs		Code	MaxDate	Table1 (2).Kurs
2	NOK	10/1/2021	100		NOK	10/1/2021	100
3	NOK	5/5/2021	200		SEK	10/5/2021	300
4	NOK	6/6/2021	300
5	SEK	6/7/2021	100
6	SEK	5/4/2021	200
7	SEK	10/5/2021	300
Sheet1

In your table find the Max Date

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Code"}, {{"MaxDate", each List.Max([StartDate]), type nullable date}})
in
    #"Grouped Rows"

Merge your query back onto itself

let
    Source = Table.NestedJoin(Table1, {"Code", "MaxDate"}, #"Table1 (2)", {"Code", "StartDate"}, "Table1 (2)", JoinKind.LeftOuter),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(Source, "Table1 (2)", {"Kurs"}, {"Table1 (2).Kurs"})
in
    #"Expanded Table1 (2)"

 

[Lars1]

Use Power Query for this result

Book2
	A	B	C	D	E	F	G
1	Code	StartDate	Kurs		Code	MaxDate	Table1 (2).Kurs
2	NOK	10/1/2021	100		NOK	10/1/2021	100
3	NOK	5/5/2021	200		SEK	10/5/2021	300
4	NOK	6/6/2021	300
5	SEK	6/7/2021	100
6	SEK	5/4/2021	200
7	SEK	10/5/2021	300
Sheet1

In your table find the Max Date

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Code"}, {{"MaxDate", each List.Max([StartDate]), type nullable date}})
in
    #"Grouped Rows"

Merge your query back onto itself

let
    Source = Table.NestedJoin(Table1, {"Code", "MaxDate"}, #"Table1 (2)", {"Code", "StartDate"}, "Table1 (2)", JoinKind.LeftOuter),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(Source, "Table1 (2)", {"Kurs"}, {"Table1 (2).Kurs"})
in
    #"Expanded Table1 (2)"

Hi

Thank you so much for your solution.
I am looking for a solution in DAX though

 

[JEC]

This could be your calculated column

=CALCULATE (
   MAX ( Table1[Starting Date] );
        ALLEXCEPT ( Table1; Table1[Code] ))

 

[Lars1]

This could be your calculated column

=CALCULATE (
   MAX ( Table1[Starting Date] );
        ALLEXCEPT ( Table1; Table1[Code] ))

Thanks for your input.

I still need this result:

And your solution comes with an error:
A single value for the 'Starting Date' column in the 'Currency Exchange Rate' table cannot be determined. This can happen when a measurement formula refers to a column that contains many values without specifying a sum function such as min, max, count or sum to form a single result.

 

[citizenbh]

Try this:

1. In Power Pivot for Excel create variable:
MaxStartDate:=MAX(Table1[StartDate])
2. From Power Pivot for Excel insert new Pivot Table
3. Insert columns (Code, StartDate, Kurs) in Rows Area
4. In Report Layout choice Show in Tabular Form
5. Disable Subtotals and Grand Totals
6. Sort StartDate: Newest to Oldest
7. Add filter "Top 10 Filter" Items 1 by MaxStartDate

 

[Lars1]

Try this:

1. In Power Pivot for Excel create variable:
MaxStartDate:=MAX(Table1[StartDate])
2. From Power Pivot for Excel insert new Pivot Table
3. Insert columns (Code, StartDate, Kurs) in Rows Area
4. In Report Layout choice Show in Tabular Form
5. Disable Subtotals and Grand Totals
6. Sort StartDate: Newest to Oldest
7. Add filter "Top 10 Filter" Items 1 by MaxStartDate

Thank you for your solution.
it seems like i have not been very clear in my request...
I need a soluition in DAX and NOT PowerPivot.

 

[citizenbh]

DAX Solution:

NewTable =
FILTER (
    'Table',
    'Table'[StartDate] = CALCULATE ( MAX ( 'Table'[StartDate] ), ALLEXCEPT ( 'Table', 'Table'[Code] )))