Have the last value of a movement table considering date and time

[Joswin_100]

Good afternoon, I ask for your support I need to know the value of the inventory at the time a sales order is dispatched.

For this I have two tables: 1) Movements in inventory (which comes by product the entries that had it) 2) Ordenes_venta (contains the detail of the order and the items that make up that order)

The dax function I'm using is IF(HASONEVALUE(Ordenes_venta[ITEM_ID]),CALCULATE(MAX(Movements[VALUE_NEW]),FILTER(Movements,Movements[DATE_TIME]<-MAX(Movements[DATE_TIME]))))

With this formula, it brings me the maximum value of the filter context date but makes no distinction at the time of the sales order. (AS SHOWN IN THE PICTURE, TABLE ORDENES_VENTA)

What I need is to bring from the movement table, the last value (Value_New) whose DATE_TIME <- to DATE_TIME of the sales order

[v-janeyg-msft · Accepted solution]

Hi, @Joswin_100 

It’s my pleasure to answer for you.

According to your description, I think you can create a measure to calculate the correct result.

Like this:

Measure =
VAR a =
    MAXX ( ALL ( 'order' ), [date-time] )
VAR b =
    MAXX ( FILTER ( ALL ( movements ), [date-time] <= a ), [date-time] )
RETURN
    SUMX ( FILTER ( ALL ( movements ), [date-time] = b ), [value new] )

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.

[amitchandak]

@Joswin_100 , how these tables are connected ? Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Joswin_100]

Hello

Thanks for your help.

In the image I show in red an example, as you will see transaction 120046171 has date of 11/08/2020 with a time of 11:15:44 pm and what I need is the VALUE_NEW field of the movements table whose date is less than or equal to that of the detail orders table. In my example I should bring the number 10 but it brings me the maximum value (17) for all transactions

My model is built like this