Last Timestamp of Previous Day

[Kolumam]

Hi All,

See my sample data below. I want to get the last value of the previous day. So for example, I need to get the last value of 19/8 which is 19/8 22:45 i.e. 7264906.5. How do I achieve using a measure?

19/8/2019 22:30	7345461.5	7264907
19/8/2019 22:31	7345461.5	7264907
19/8/2019 22:32	7345461.5	
19/8/2019 22:33	7345461.5	7264907
19/8/2019 22:34	7345461.5	7264907
19/8/2019 22:35	7345461.5	7264907
19/8/2019 22:36	7345461.5	7264907
19/8/2019 22:37	7345461.5	7264907
19/8/2019 22:38	7345461.5	7264907
19/8/2019 22:39	7345461.5	7264907
19/8/2019 22:40	7345461.5	7264907
19/8/2019 22:41	7345461.5	7264907
19/8/2019 22:42	7345461.5	
19/8/2019 22:43	7345461.5	7264907
19/8/2019 22:44	7345461.5	7264906.5
19/8/2019 22:45	7345461.5	7264906.5
20/8/2019 0:00	7345461.5	7264904
20/8/2019 0:01	7345461.5	7264904
20/8/2019 0:02	7345461.5	7264904
20/8/2019 0:03	7345461.5	7264903.5
20/8/2019 0:04	7345461.5	7264903.5

[dax · Accepted solution]

Hi Kolumam, 

My sample:

dateamountid

19/8/2019 22:30	7345461.5	7264907
19/8/2019 22:31	7345461.5	7264907
19/8/2019 22:32	7345461.5
19/8/2019 22:33	7345461.5	7264907
19/8/2019 22:34	7345461.5	7264907
19/8/2019 22:35	7345461.5	7264907
19/8/2019 22:36	7345461.5	7264907
19/8/2019 22:37	7345461.5	7264907
19/8/2019 22:38	7345461.5	7264907
19/8/2019 22:39	7345461.5	7264907
19/8/2019 22:40	7345461.5	7264907
19/8/2019 22:41	7345461.5	7264907
19/8/2019 22:42	7345461.5
19/8/2019 22:43	7345461.5	7264907
19/8/2019 22:44	7345461.5	7264906.5
19/8/2019 22:45	7345461.5	7264906.5
20/8/2019 0:00	7345461.5	7264904
20/8/2019 0:01	7345461.5	7264904
20/8/2019 0:02	7345461.5	7264904
20/8/2019 0:03	7345461.5	7264903.5
20/8/2019 0:04	7345461.5	7264903.5

You could try below measure (because today is 8/21, so it will sjow 8/20 result, if you want to see 8/19, you could use today()-2)

Measure 5 =
VAR maxd =
    CALCULATE (
        MAX ( tets[date] ),
        FILTER ( ALL ( tets ), tets[date].[Date] <= TODAY () - 1 )
    )
RETURN
    CALCULATE ( MIN ( tets[id] ), FILTER ( tets, tets[date] = maxd ) )

 

Best Regards,
Zoe Zhi

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

[AlB]

Hi @Kolumam 

You don't describe how you want to use the measure so I'll conjecture. 

1. Since you are looking at day level and you have timestamps on the table, create a calculated column with only the day and convert to type date. Table1[TimeStamp] is the first column you show:

Day = INT(Table1[TimeStamp])

2. Place Table1[Day] in the rows of a matrix visual

3. Create this measure and place it in the matrix visual

Measure =
VAR LastTimePreviousDay =
    CALCULATE (
        MAX ( Table1[TimeStamp] ),
        FILTER ( ALL ( Table1[Day] ), Table1[Day] < SELECTEDVALUE ( Table1[Day] ) )
    )
RETURN
    CALCULATE (
        DISTINCT ( Table1[Value] ),
        FILTER ( ALL ( Table1[Day] ), Table1[TimeStamp] = LastTimePreviousDay )
    )