fitler for latest timestamp with different date powerbi help

[teepeeee123]

how do i sort for the latest timestamp for each individual date.

Date                            value

11/01/24 7:00 am        5

11/01/24 9:00 am        7

11/01/24 10:00 am        4

12/01/24 4:00 am        5

12/01/24 7:00 am        5

12/01/24 9:00 am        5

and returns/filters

Date                            value

11/01/24 10:00 am       4

12/01/24 9:00 am        5

[ryan_mayu · Accepted solution]

@teepeeee123 

in pq, split the date and time column

= Table.AddColumn(#"Inserted Date", "Time", each Time.From([date]), type time)

= Table.AddColumn(#"Inserted Date", "Time", each Time.From([date]), type time)

then you can use dax to create a  new column

Column = if('Table'[Time]=CALCULATE(max('Table'[Time]),ALLEXCEPT('Table','Table'[Date.1])),"Last Value")

at last you can use the new column to filter

pls see the attachment below

[Anonymous · Accepted solution]

Hi @teepeeee123 ,

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

1.Create a culculate table

Latest Record by Date = 
SUMMARIZE(
    'Table',
    'Table'[Date].[Date], 
    "Latest Value", MAXX(
        FILTER(
            'Table',
            'Table'[Date].[Date] = EARLIER('Table'[Date].[Date])
        ),
        'Table'[Date]
    )
)

2.Confirms that there is a 1-to-1 relationship between date and latest Value

3.Use Latest value to filter the final table

Best regards

Albert He

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

[ryan_mayu · Accepted solution]

@teepeeee123 

in pq, split the date and time column

= Table.AddColumn(#"Inserted Date", "Time", each Time.From([date]), type time)

= Table.AddColumn(#"Inserted Date", "Time", each Time.From([date]), type time)

then you can use dax to create a  new column

Column = if('Table'[Time]=CALCULATE(max('Table'[Time]),ALLEXCEPT('Table','Table'[Date.1])),"Last Value")

at last you can use the new column to filter

pls see the attachment below