Sum of the stock quantity, if I only use the last stock entry value

[Stewwe]

Dear Powerbi community,

I have been trying to solve the following problem for a few days now, but unfortunately I can't manage it. 😕

So: I have a table "Table1" with stock values, but I only ever get the last stock value entry when the stock has changed.

E.g.

Date	Article	Stockvalue
18.6.21	Product A	5
17.6.21	Product A	4
15.6.21	Product A	3
18.6.21	Product B	4
15.6.21	Product B	2

Now I would like to have a measure "Stockvalue_Measure" that gives me the following end-results in matrix:

Date	Stockvalue_Measure
18.6.21	5+4=9
17.6.21	4+2(Product 15.6.21)=6
16.6.21	3 (Prodcut A 15.6.21)+2 (Prodcut B 15.6.21) = 5
15.6.21	2+3=5

I think that I first have to do a precalculation with the variables and then in the second step I have to sum (sumx):

Stockvalue_Measure=

VAR Lastknowndate =

MAX ( Date[Date] )

VAR lastknowninventory =

CALCULATETABLE (

SUMMARIZE (

Table1,

Table1[article],

Table1[Date],

"inventory",

CALCULATE(LASTNONBLANKVALUE(Date[Date],sum(Stockvalue),Date[Date]<Lastknowndate)

)

)

RETURN

SUMX ( lastknowninventory, [inventory] )

But as you can guess, it doesn´t work 😉

Grateful for any help

Stewwe

[Stewwe · Accepted solution]

Hello @Jihwan_Kim ,

it works, too. I was able to speed it up a bit, but it is still too slow to use it productively 50000ms

lastnonblankvalue measure =

CALCULATE (

LASTNONBLANKVALUE ( Dates[Date], CALCULATE ( SUM ( Data[Stockvalue] ) ) ),

FILTER ( ALL ( Dates[Date]), Dates[Date] <= MAX ( Dates[Date] ) )

)

We are so close to the solution and it still doesn't work...hope we still make it 😉

Steffen

[parry2k · Accepted solution]

@Stewwe interesting is it possible to share the large dataset, remove any other information from the file, just keep the relevant one. I would like to test and improve the dax on the larger set otherwise it is hard to optimize the queries.

[Anonymous]

Hi @Stewwe,

have you found a faster solution yet? I'm currently struggeling with the same problem.

I have used the 

lastnonblankvalue measure =

CALCULATE (

LASTNONBLANKVALUE ( Dates[Date], CALCULATE ( SUM ( Data[Stockvalue] ) ) ),

FILTER ( ALL ( Dates[Date]), Dates[Date] <= MAX ( Dates[Date] ) )

)

but the calculation is still incredibly slow.

@parry2k  @Jihwan_Kim  any recommendations on how to make it any faster? 

I would really appreciate any further ideas on how to optimise the performance.

Thanks!

[Stewwe]

Thanks for your help. Unfortunately it will take until Monday 28.6 to prepare something.

Bye

Stewwe

[parry2k]

@Stewwe here is the measure, the solution is attached too. You need to create a star schema, with two dimensions, article, and date.

Stock from recent date = 
VAR __table = 
SUMMARIZE ( 
    CROSSJOIN ( 
        FILTER ( 
            ALL ( 'Calendar'[Date] ), 
            'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
        ), 
        ALL ( Article[Article] ) 
    ), 
    Article[Article], 
    "@Stock", MAX ( Stock[Stock] )  
)
RETURN
SUMX (  __table, [@Stock] )

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Stewwe]

Hello parry2k,

Thank you very much for your solution. The sample data is exactly what I need as a result.

Unfortunately, the solution is clearly too slow with the size of the real data - PowerBI aborts the evaluation.

My tables have the following size:
Article =38,000 rows
Table1= 800.000row>
Date = 4300 rows

If you have a more performant solution, I would be very happy to hear from you!

Bye stewwe

[Jihwan_Kim]

lastnonblankvalue measure =

CALCULATE (

LASTNONBLANKVALUE ( Data[Date], CALCULATE ( SUM ( Data[Stockvalue] ) ) ),

FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )

)

Stockvalue total by lastnonblank of each article =

VAR newtable =

ADDCOLUMNS (

VALUES ( Articles[Article] ),

"@lastnonblankvalue", [lastnonblankvalue measure]

)

RETURN

IF ( ISFILTERED ( Dates[Date] ), SUMX ( newtable, [@lastnonblankvalue] ) )

https://www.dropbox.com/s/26fnrness891bnk/stewwe.pbix?dl=0 

[Stewwe]

Wow @Jihwan_Kim , the solution looks very good and so fast feedback! Many thanks already 🙂

Now I only have one problem:

The calculation takes a very, very long 160000 ms.

My tables have the following size:
Article =38,000 rows
Table1= 800.000row>
Date = 4300 rows

Do you have an approach on how we can speed this up?

Thank you in advance!

[Jihwan_Kim]

Thanks for your feedback.

I am not sure if it would work, but please try to replace the [lastnonblankvalue measure =] with the below.

lastnonblankvalue measure =

CALCULATE (

LASTNONBLANKVALUE ( Dates[Date], CALCULATE ( SUM ( Data[Stockvalue] ) ) ),

FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )

)

[Stewwe · Accepted solution]

Hello @Jihwan_Kim ,

it works, too. I was able to speed it up a bit, but it is still too slow to use it productively 50000ms

lastnonblankvalue measure =

CALCULATE (

LASTNONBLANKVALUE ( Dates[Date], CALCULATE ( SUM ( Data[Stockvalue] ) ) ),

FILTER ( ALL ( Dates[Date]), Dates[Date] <= MAX ( Dates[Date] ) )

)

We are so close to the solution and it still doesn't work...hope we still make it 😉

Steffen