Doble condition

Bayeta

New Member
Joined
Apr 5, 2019
Messages
4
Hi!

I've a problem in DAX I can't solve. Perhaps you could help me...

In an stock movement table, i have the next columns: Date-time, Product id, Movement id, Order id and Stock.

The final stock value for each product is the number that appears in the column "Stock" in the last date-time and in the last movement for each product.

A product can have multiple movements on the same date-time, so I must figure out the MAX date-time AND the MAX movement id for each product and wrap it up in a CALCULATE.

It seems as a simple DAX formula, but I cant get to it...

Thank you very much in advance!

Baye
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,250
Well you will have to try a few things. The basic pattern is

Code:
LAST Stock =VAR laststock= MAX ( Table[DATE] )
RETURN
    CALCULATE (
        SUM ( tables[stock]),
        'DATE'[DATE] = laststock
    )

I guess you should try adding another filter

Code:
LAST Stock =VAR oldestdate = MAX ( Table[DATE] )
VAR biggestMovement = max(table[movement id])
RETURN
    CALCULATE (
        SUM ( tables[stock]),
        'DATE'[DATE] = oldestdate,
	 table[movement id] = biggestmovement
    )

The only thing I am not sure about is if these 2 filters will conflict with each other. If that doesn't work, please post a sample workbook showing the issue.
 

Bayeta

New Member
Joined
Apr 5, 2019
Messages
4
Hi!

First of all thank you very much for the quick reply!

I'm afraid it doesn't work, perhaps if the fact that I'm using excel's powerpivot and not PowerBI?

I have uploaded the file here as an example and tried to replicate your formula:

https://we.tl/t-thUHY6KBEC

I can´t get to it...thank you very much in advance!!!

Baye








Well you will have to try a few things. The basic pattern is

Code:
LAST Stock =VAR laststock= MAX ( Table[DATE] )
RETURN
    CALCULATE (
        SUM ( tables[stock]),
        'DATE'[DATE] = laststock
    )

I guess you should try adding another filter

Code:
LAST Stock =VAR oldestdate = MAX ( Table[DATE] )
VAR biggestMovement = max(table[movement id])
RETURN
    CALCULATE (
        SUM ( tables[stock]),
        'DATE'[DATE] = oldestdate,
     table[movement id] = biggestmovement
    )

The only thing I am not sure about is if these 2 filters will conflict with each other. If that doesn't work, please post a sample workbook showing the issue.
 

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,250
It is nothing to do with Excel. I can't open your Excel workbook (it says it is corrupt), but I could copy it to Power BI. This is what you need to do.
1. split the date and time into 2 separate columns.
2. Load a calendar table https://exceleratorbi.com.au/power-pivot-calendar-tables/
3. Join the tables on data, and put the Date from Calendar in a visual
4. From what I can see, the MvtID keeps incrementing, so i don't think you need the time stamp.

This measure should give you the last stock on hand
Code:
SOH =SUMX (
    VALUES ( Stock[IdProduct] ),
    CALCULATE (
        SUM ( Stock[Stock] ),
        FILTER ( ALL ( Stock[Number] ), Stock[Number] = MAX ( Stock[Number] ) )
    )
)
 

Bayeta

New Member
Joined
Apr 5, 2019
Messages
4

ADVERTISEMENT

Hi Matt!

I'm really sorry but that's not the point as the problem is that the last number doesn't always correspond to the SoH.

IdProduct

<tbody>
</tbody>
DateTime

<tbody>
</tbody>
Number

<tbody>
</tbody>
Stock

<tbody>
</tbody>
M4290282

<tbody>
</tbody>
30/01/2019 9:00:23

<tbody>
</tbody>
7806165

<tbody>
</tbody>
10.810

<tbody>
</tbody>
M4290282

<tbody>
</tbody>
30/01/2019 9:01:08

<tbody>
</tbody>
7806282

<tbody>
</tbody>
10.804

<tbody>
</tbody>
M4290282

<tbody>
</tbody>
30/01/2019 11:15:45

<tbody>
</tbody>
7805376

<tbody>
</tbody>
10.798

<tbody>
</tbody>
M3015495

<tbody>
</tbody>
30/01/2019 9:07:38

<tbody>
</tbody>
7807378

<tbody>
</tbody>
184.906

<tbody>
</tbody>
M3015495

<tbody>
</tbody>
30/01/2019 9:07:43

<tbody>
</tbody>
7807397

<tbody>
</tbody>
183.982

<tbody>
</tbody>
M3015495

<tbody>
</tbody>
30/01/2019 15:58:37

<tbody>
</tbody>
7827286

<tbody>
</tbody>
177.249

<tbody>
</tbody>
M3015495

<tbody>
</tbody>
31/01/2019 17:51:50

<tbody>
</tbody>
7807753

<tbody>
</tbody>
177.249

<tbody>
</tbody>
M3015495

<tbody>
</tbody>
31/01/2019 17:51:50

<tbody>
</tbody>
7807752

<tbody>
</tbody>
170.495

<tbody>
</tbody>

<tbody>
</tbody>

I've copied part of the table...

As you can see in the first product (M4290282), the SoH correspond to the last date-time, but doesn't correspond to the last number, as the last datetime only has one movement.

In the second product (M3015495), the last datetime has two movements, in this case, as there are two movements with the same datetime, the last movement is the correct stock.

Thank you!!!!

Baye




It is nothing to do with Excel. I can't open your Excel workbook (it says it is corrupt), but I could copy it to Power BI. This is what you need to do.
1. split the date and time into 2 separate columns.
2. Load a calendar table https://exceleratorbi.com.au/power-pivot-calendar-tables/
3. Join the tables on data, and put the Date from Calendar in a visual
4. From what I can see, the MvtID keeps incrementing, so i don't think you need the time stamp.

This measure should give you the last stock on hand
Code:
SOH =SUMX (
    VALUES ( Stock[IdProduct] ),
    CALCULATE (
        SUM ( Stock[Stock] ),
        FILTER ( ALL ( Stock[Number] ), Stock[Number] = MAX ( Stock[Number] ) )
    )
)
 

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,250
then just replace the stock number with the date-time
Code:
SOH =SUMX (    VALUES ( Stock[IdProduct] ),
    CALCULATE (
        SUM ( Stock[Stock] ),
        FILTER ( ALL ( Stock[date-time] ), Stock[date-time] = MAX ( Stock[date-time] ) )
    )
)
 

Bayeta

New Member
Joined
Apr 5, 2019
Messages
4
Hi Matt,

That formula works only for a case, same as the previous formula, both work, one for a case, and the other one for the other, but I need them to work in both cases (when last date is unique, last date and not last number; and when last date is repeated, last movement in the repeated date).

Check this please, i've tried to explain it again from scratch.

https://powerpivotforum.com.au/viewtopic.php?f=6&t=1477

Thank you for your help!

B.

then just replace the stock number with the date-time
Code:
SOH =SUMX (    VALUES ( Stock[IdProduct] ),
    CALCULATE (
        SUM ( Stock[Stock] ),
        FILTER ( ALL ( Stock[date-time] ), Stock[date-time] = MAX ( Stock[date-time] ) )
    )
)
 

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
If I understand what you are trying correctly, you could add a calculated column with a formula of

Code:
=[Number]=CALCULATE(MAX([Number]),FILTER(Stock,[DateTime]=EARLIER([DateTime])))

I called it Latest, then add a slicer for latest and select True
 

Watch MrExcel Video

Forum statistics

Threads
1,122,606
Messages
5,597,134
Members
414,128
Latest member
Jorglo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top