Doble condition

[Bayeta]

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

 

[Matt Allington]

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

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

I guess you should try adding another filter

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]

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

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

I guess you should try adding another filter

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]

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

SOH =SUMX (
    VALUES ( Stock[IdProduct] ),
    CALCULATE (
        SUM ( Stock[Stock] ),
        FILTER ( ALL ( Stock[Number] ), Stock[Number] = MAX ( Stock[Number] ) )
    )
)

 

[Bayeta]

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

SOH =SUMX (
    VALUES ( Stock[IdProduct] ),
    CALCULATE (
        SUM ( Stock[Stock] ),
        FILTER ( ALL ( Stock[Number] ), Stock[Number] = MAX ( Stock[Number] ) )
    )
)

 

[Matt Allington]

then just replace the stock number with the date-time

SOH =SUMX (    VALUES ( Stock[IdProduct] ),
    CALCULATE (
        SUM ( Stock[Stock] ),
        FILTER ( ALL ( Stock[date-time] ), Stock[date-time] = MAX ( Stock[date-time] ) )
    )
)

 

[Bayeta]

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

SOH =SUMX (    VALUES ( Stock[IdProduct] ),
    CALCULATE (
        SUM ( Stock[Stock] ),
        FILTER ( ALL ( Stock[date-time] ), Stock[date-time] = MAX ( Stock[date-time] ) )
    )
)

 

[theBardd]

If I understand what you are trying correctly, you could add a calculated column with a formula of

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

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