topic Lastnonblank with Date and Hour in DAX Commands and Tips

Lastnonblank with Date and Hour

Anonymous — Mon, 17 Jan 2022 14:19:36 GMT

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:

Re: Lastnonblank with Date and Hour

smpa01 — Mon, 17 Jan 2022 14:33:50 GMT

Anonymous provide the sample data in table format here

Re: Lastnonblank with Date and Hour

Anonymous — Mon, 17 Jan 2022 14:53:21 GMT

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])

)

Re: Lastnonblank with Date and Hour

smpa01 — Mon, 17 Jan 2022 15:37:16 GMT

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] ) )

Re: Lastnonblank with Date and Hour

Anonymous — Mon, 17 Jan 2022 17:11:41 GMT

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.

Re: Lastnonblank with Date and Hour

smpa01 — Mon, 17 Jan 2022 17:22:33 GMT

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.

Re: Lastnonblank with Date and Hour

Anonymous — Mon, 17 Jan 2022 17:37:40 GMT

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

Re: Lastnonblank with Date and Hour

smpa01 — Mon, 17 Jan 2022 18:19:35 GMT

Anonymous what is the relationship between these two tables?

Re: Lastnonblank with Date and Hour

Anonymous — Mon, 17 Jan 2022 18:35:35 GMT

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

Re: Lastnonblank with Date and Hour

smpa01 — Mon, 17 Jan 2022 18:48:21 GMT

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

Re: Lastnonblank with Date and Hour

Anonymous — Mon, 17 Jan 2022 19:59:11 GMT

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

Re: Lastnonblank with Date and Hour

Anonymous — Mon, 17 Jan 2022 20:21:13 GMT

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