Lastnonblank with Date and Hour

[Anonymous]

Hi there, 

I'm trying to develop a dash that gets the dock status at the time of order, must select the last modification date and time value, but i can't make it work.

It was expected that orders placed after 15:00 the value 0

The table below shows the values to be searched for:

[smpa01]

@Anonymous  you can create a column like this

Column =
VAR _x =
    FILTER ( Pedidos, Pedidos[Data] = MAX ( Pedidos[Data] ) )
RETURN
    IF ( Handling[Data e hora] >= MAXX ( _x, [Data] ), MAXX ( _x, [Doca] ) )

[Anonymous]

but I need data in all columns that have dock modification, in which case it's only showing from the last day. I needed something like the print I sent, but the values shouldn't all return 1.

[smpa01]

@Anonymous  I am not sure what do you mean.

The max datetime in Handling is 2021-12-13 03:00:00 PM

Do you want to identify the rows in Pedidos that has Pedios[Data e hora] greater than MAX(

Handling[Data]) and get the corresponding value? I am not understanding correctly.

[Anonymous]

@smpa01I need to know the status of the dock at the time of order, like this example:

Order date	Expected result		Last modification	Status
17/01/2022 08:00	0		17/01/2022 05:00	0
17/01/2022 11:15	1		17/01/2022 11:00	1
17/01/2022 13:00	1		17/01/2022 15:00	0
17/01/2022 14:00	1
17/01/2022 15:30	0
17/01/2022 16:00	0

[smpa01]

@Anonymous  what is the relationship between these two tables?

[Anonymous]

@smpa01  The table I sent at the beginning was for tests I was doing.

The dock status table changes at certain times by changing the values between 0, 1 and 2

The order table I need to have the dock status at the time of order.

For example:
At 08:00 AM - status 2
At 10:00 AM - status 1
At 02:00 PM - status 0

Orders created between 08:00 AM and 09:59 AM must return dock 2 status
Orders created between 10:00 AM and 01:59 PM must return dock 1 status
Orders created after 14:00 PM must return dock status 0

[smpa01]

@Anonymous  can you please create a sample pbix, upload in 1/g drive or any other file hosting service and share the url here?

[Anonymous]

Link to Download
https://mega.nz/file/KN8mnIjS#ao-GxUXBTerA6W2XJ2Hkld9vJoU60PM1XvccmFeaH0Y

[Anonymous · Accepted solution]

@smpa01I got it, thank you very much!!!

Solution:

data alteração =

var data = CALCULATE(

lastNONBLANK(Handling[Data], true()),

FILTER(ALL(Handling[Data]),

Handling[Data] <= Pedidos[Data e hora])

)

var handl = LOOKUPVALUE(Handling[Status doca],Handling[Data],data)

return

handl

[smpa01]

@Anonymous  provide the sample data in table format here

[Anonymous]

Orders

Pedido	Data e hora	Quantidade
17	09/12/2021 14:00	161
18	09/12/2021 15:30	444
19	10/12/2021 14:00	434
20	10/12/2021 15:30	414
21	11/12/2021 14:00	420
22	11/12/2021 15:30	338
23	12/12/2021 14:00	244
24	12/12/2021 15:30	230
25	13/12/2021 14:00	430
26	13/12/2021 15:30	153

Status dock

Data	Doca
12/12/2021 14:00	1
12/12/2021 15:00	0
13/12/2021 08:00	1
13/12/2021 15:00	0
11/12/2021 06:00	1
11/12/2021 15:00	0

Dax 

Handling = CALCULATE(

LASTNONBLANK(Handling[Doca], true()),

FILTER(ALL(Handling[Data]),

Handling[Data] <= Pedidos[Data e hora])

)