How do i pick up the latest value for a datetime column in DAX or M Query and put it in buckets?

[Anonymous]

Hi,

Need help. Thank you.

Below is the table and i want 3 buckets out of it.

Bucket 1

For the Date1 get me the value of flag where my datetime2 column  = Date1+7AM

Meaning if my date1 column is 19/11/2020 get me the last flag value before 7 am for 20/11/2020

Bucket 2

If my date1 is 20/11/2020 Get me the last flag value before 7 AM for the next day instead meaning this can range between 20/11/2020 00: 00: 01  to 21/11/2020 06:59:59

Bucket 3

Get me the last recorded value of the flag based on datetime2 column

ID	Date1	DateTime2	Flag
1	19/11/2020	20/11/2020 3:35:35	S
1	19/11/2020	20/11/2020 6:35:35	B
1	19/11/2020	20/11/2020 15:05:35	B
1	19/11/2020	20/11/2020 15:35:35	P
1	19/11/2020	21/11/2020 1:35:35	P
1	19/11/2020	23/11/2020 1:35:35	M
Bucket 1
1	19/11/2020	20/11/2020 6:35:35	B
Bubket 2
1	19/11/2020	20/11/2020 15:35:35	P
Bucket 3
1	19/11/2020	23/11/2020 1:35:35	M

[Anonymous]

Thank you. This works well. I also found another solution as well where i derived the max(datetime2) value and put it in filter 

something like filter(tablename(datetime2  = max(datetime2))

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

It’s my pleasure to answer for you.

According to your description,I think you can create three measures to calculate the desired result.(Your budget 2 and Bucket2 are duplicated)

Like this:

Measure = 
IF (
    SELECTEDVALUE ( 'Table'[  DateTime2] )
        = MAXX (
            FILTER (
                ADDCOLUMNS (
                    ALL ( 'Table' ),
                    "flag", IF ( DATEDIFF ( [ Date1], [  DateTime2], HOUR ) < 31, 1, 0 )
                ),
                [flag] = 1
                    && [ID] = SELECTEDVALUE ( 'Table'[ID] )
            ),
            [  DateTime2]
        ),
    1,
    0
)

Measure2 = 
IF (
    SELECTEDVALUE ( 'Table'[  DateTime2] )
        = MAXX (
            FILTER (
                ALL ( 'Table' ),
                [ID] = SELECTEDVALUE ( 'Table'[ID] )
                    && [ Date1] = SELECTEDVALUE ( 'Table'[ Date1] )
            ),
            [  DateTime2]
        ),
    1,
    0
)

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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