Double condition in Stock table

Anything related to PowerPivot and DAX Formuale
Bayeta
Posts: 1
Joined: Mon Apr 08, 2019 4:51 pm

Double condition in Stock table

Postby Bayeta » Mon Apr 08, 2019 9:20 pm

Hi!

I have a problem with a two condition formula.

In my stock table, the stock on hand correspond to the last date for each product, but in the cases where there are two movements in the same date for the same product, the last movement (max number id) between those is the stock on hand. Note that there could be a movement with a higher number before that last date.

I must create a formula that filters my table for each product by the last date, and then find the max number between those movements.

One formula i've tried is:
LastStockDATE:=SUMX(VALUES(MOVIMTO[productID]);CALCULATE (
SUM ( MOVIMTO[Stock] );
FILTER ( ALL ( MOVIMTO[Datetime] ); MOVIMTO[Datetime] = MAX ( MOVIMTO[Datetime] ) )
)
)

Other formula is:
LastStockNUMERO:=SUMX(VALUES(MOVIMTO[ProductId]);CALCULATE (
SUM ( MOVIMTO[Stock] );
FILTER ( ALL ( MOVIMTO[NumberId] ); MOVIMTO[NumberId] = MAX ( MOVIMTO[NumberId] ) )
)
)

The former works when the last date has only one movement. The latter, works when the last movement happens in the last date.

But when we have two movements in the same last date they don't work.

I need to combine both conditionals in a formula...

https://1drv.ms/u/s!Al6VLYxbJKorfNCuY45rCXwcdeM

Thank you in advance!

B.

PhilC
Posts: 219
Joined: Tue Sep 09, 2014 8:13 am

Re: Double condition in Stock table

Postby PhilC » Thu Apr 11, 2019 11:24 am

Hi Bayeta,

Would be easier to delve into this if you provide a mock up of the data/model and the formulas you have so far.

Cheers
Phil


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 0 guests

cron