Doble condition

B

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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
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.
 
Upvote 0
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








Matt Allington said:
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.
Click to expand...
 
Upvote 0
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] ) )
    )
)
 
Upvote 0
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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80"]IdProduct[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 134"]
<tbody>[TR="class: grid"]
[TD="width: 134"]DateTime[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80"]Number[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80"]Stock[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80"]M4290282[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 134"]
<tbody>[TR="class: grid"]
[TD="width: 134, align: right"]30/01/2019 9:00:23[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80, align: right"]7806165[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80, align: right"]10.810[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80"]M4290282[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 134"]
<tbody>[TR="class: grid"]
[TD="width: 134, align: right"]30/01/2019 9:01:08[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80, align: right"]7806282[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80, align: right"]10.804[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80"]M4290282[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 134"]
<tbody>[TR="class: grid"]
[TD="width: 134, align: right"]30/01/2019 11:15:45[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80, align: right"]7805376[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80, align: right"]10.798[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80"]M3015495[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 134"]
<tbody>[TR="class: grid"]
[TD="width: 134, align: right"]30/01/2019 9:07:38[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80, align: right"]7807378[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80, align: right"]184.906[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80"]M3015495[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 134"]
<tbody>[TR="class: grid"]
[TD="width: 134, align: right"]30/01/2019 9:07:43[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80, align: right"]7807397[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80, align: right"]183.982[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80"]M3015495[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 134"]
<tbody>[TR="class: grid"]
[TD="width: 134, align: right"]30/01/2019 15:58:37[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80, align: right"]7827286[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80, align: right"]177.249[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80"]M3015495[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 134"]
<tbody>[TR="class: grid"]
[TD="width: 134, align: right"]31/01/2019 17:51:50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80, align: right"]7807753[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80, align: right"]177.249[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80"]M3015495[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 134"]
<tbody>[TR="class: grid"]
[TD="width: 134, align: right"]31/01/2019 17:51:50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80, align: right"]7807752[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 80"]
<tbody>[TR="class: grid"]
[TD="width: 80, align: right"]170.495[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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




Matt Allington said:
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] ) )
    )
)
Click to expand...
 
Upvote 0
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] ) )
    )
)
 
Upvote 0
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.

Matt Allington said:
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] ) )
    )
)
Click to expand...
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Power Query Help to Calculate Stock Splits
Replies
0
Views
157
rasinc
R
jimrward
  • Question Question
Add column with Max date from another table
Replies
4
Views
324
jimrward
jimrward
I
  • Question Question
Stock Cover calculation
Replies
2
Views
982
illusionek
I
S
  • Question Question
Best initial design setup for large table (One set of dates in column A)
Replies
0
Views
409
spurs50
S
B
  • Question Question
Report Inventory needs to support customer order with flow through assembly tree when in excess of parent part stock available.
Replies
0
Views
834
bjuntz
B

Forum statistics

Threads
1,223,888
Messages
6,175,213
Members
452,618
Latest member
Tam84

Share this page

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
Back
Top