Table visualization/ display value based on max date

[Raj_251891]

Hello, I am new to power BI and need you help on below:

I have the data as below:

alias_no	dest	Msg_cod	Ref No	Src	status	ID	Date & Time
	W	W-002	PEES1001	W	success	81	21/9/2020 11:23:51 AM
	W	W-003	PEES1001	W	success	81	21/9/2020 11:23:53 AM
	W	W-004	PEES1001	W	success	81	21/9/2020 11:23:55 AM
	I	W-001	PEES1001	W	success	81	21/9/2020 11:23:49 AM
	T	W-005	PEES1001	W	success	81	21/9/2020 11:23:57 AM
	I	W-006	PEES1001	W	success	81	21/9/2020 11:24:00 AM
	M	E-001	PEES1001	E	success	81	21/9/2020 11:23:39 AM
	E	E-004	PEES1001	E	success	81	21/9/2020 11:23:41 AM
	M	E-005	PEES1001	E	success	81	21/9/2020 11:23:43 AM
	I	E-006	PEES1001	E	success	81	21/9/2020 11:23:45 AM
	W	I-001	PEES1001	I	success	81	21/9/2020 11:23:47 AM
	M	I-006	PEES1001	C	success	81	21/9/2020 11:24:02 AM

I am trying to create a table visualization to show the latest values based on the max date. Expected data as below:

Ref No	Date & Time	I-006	Src	dest	status
PEES1001	21/9/2020 11:24:02 AM	I-006	C	M	success

Thanks
Raj 

[Ashish_Mathur]

Hello

You can download my solution file from here.

I hope this helps.

[Anonymous]

Hi  @Raj_251891 ,

Here are the steps you can follow：

1. Create Calculation Table.

Table 3 = FILTER('Table',
'Table'[Date & Time]=MAXX('Table','Table'[Date & Time]))

2. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @Raj_251891 ,

make sure that the column Date & Time has the data type Date/Time assigned ,

I changed the data type accordingly using Power Query:

I chose the settings below:

After that I created a calculated column using DAX (doing this, allows to filter the data using a slicer).

I used this DAX statement:

is latest = 
var __currentDateTime = [Date & Time]
var __maxDateTime = CALCULATE( MAX( 'Table'[Date & Time] ) , 'Table' )
return
IF( __currentDateTime = __maxDateTime  , 1 , 0 )

Finally my table will look like this:

Hopefully, this is what you are looking for.

Regards,

Tom

[amitchandak]

@Raj_251891 , All the columns other than Table[Date & Time], do this all column. Create a measure like this and use in Table visual

Max Ref No= lastnonblankvalue(Table[Date & Time], max(Table[Ref No]))