Creating measure selecting latest date and time

[MrHest]

Hi there,

Im looking for help to write the correct measure to pick the price based on the latest date and time.

A simplified version of my data set looks like this :

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Price[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]15,2[/TD]
[TD]21.05.2019 10:00[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]15,9[/TD]
[TD]21.05.2019 10:10[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]15,5[/TD]
[TD]21.05.2019 10:20[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]14,7[/TD]
[TD]20.05.2019 19:00[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]14,9[/TD]
[TD]20.05.2019 19:30[/TD]
[/TR]
</tbody>[/TABLE]


Im looking for the outcome where i write a measure, and it picks the latest observation, and in this case display the price 15,50 from the newest observation made 21.05.2019 at 10:20

 

[Special-K99]

There's nothing in the data that reflects the word "measure"
You "write a measure"
What is measure?
Is it ID?
Then why not call it ID in your description?

 

[sandy666]

Create a Measure in Power Pivot

 

[Special-K99]

Is this what you mean?

in D1 enter an ID
in D2
=MAX(IF(D1=A$2:A$6,C$2:C$6))
Array formula, use Ctrl-Shift-Enter

 

[Special-K99]

Is this what you mean?

in D1 enter an ID
in D2
=MAX(IF(D1=A$2:A$6,C$2:C$6))
Array formula, use Ctrl-Shift-Enter

returns latest date
Then just use

=INDEX(B$2:B$6,MATCH(MAX(IF(D1=A$2:A$6,C$2:C$6)),C$2:C$6,0),1)
to get the price
(assuming there are not identical dates/times in column C)

 

[Ozeroth]

Hi,

To filter on the 'maximum' value of any column within a DAX measure, the LASTNONBLANK is a convenient way of doing this:

Price at latest time =
CALCULATE ( 
    AVERAGE ( YourTable[Price] ),
    LASTNONBLANK ( YourTable[Time], 0 )
)

You need to aggregate Price in some way, so AVERAGE is an arbitrary aggregation that is probably appropriate for Price.

Regards,
Owen