Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm building a report for stock transactions and need to summarize the whole stock value.
The problem is that only rows with deliverys have a specified price, not the sales rows. So I need to add it for them.
I need a formula that, per article, gets price from last delivery row, so that I can multiply it with the stock amount of the current row.
It's going to be presented in a table that can be drilled down from item groups to articles, so it needs to work for both.
I guess that a measure is the way to go, but I can't seem to figure out the details!
This is what the table looks like:
And this is what I want to end up with:
Solved! Go to Solution.
For others who might have the same problem:
I did a turn around and thought about other options.
What I ended up with was to go back to Power Query, sort the table on Item and then sort om Datetime. After that I used the "fill function" to get values on all rows for "Price /ea". After that I created a new column with "Price /ea" x "Stock change".
Problem solved!
For others who might have the same problem:
I did a turn around and thought about other options.
What I ended up with was to go back to Power Query, sort the table on Item and then sort om Datetime. After that I used the "fill function" to get values on all rows for "Price /ea". After that I created a new column with "Price /ea" x "Stock change".
Problem solved!
Hi @simonfalun,
Please refer to below measure:
Price from last delivery = VAR previousdatetime = CALCULATE ( MAX ( Stock[Datetime] ), FILTER ( ALLSELECTED( Stock ), Stock[Datetime] < MAX ( Stock[Datetime] ) && Stock[SKU] = SELECTEDVALUE ( Stock[SKU] ) && Stock[Price/ea] <> BLANK () ) ) RETURN IF ( SELECTEDVALUE ( Stock[Price/ea] ) = BLANK (), CALCULATE ( SUM ( Stock[Price/ea] ), FILTER ( ALLSELECTED ( Stock ), Stock[SKU] = SELECTEDVALUE ( Stock[SKU] ) && Stock[Datetime] = previousdatetime ) ), SUM ( Stock[Price/ea] ) )
Best regards,
Yuliana Gu
I realize that I wasn't thinking right.
Perhaps its best to includ the multiplication in the formula, to get the "row total" (Stock change x Price from last delivery)
The formula proviced by v-yulgu-msft doesn't calculate on item group level, just on item level - as shown below.
Maybe I'll have to use something like SUMX?
I tweaked it a little bit to provide Stock change x Price from last delivery:
VAR StockChange = SUM( Stock[Stock change] ) VAR previousdatetime = CALCULATE ( MAX ( Stock[Datetime] ); FILTER ( ALLSELECTED( Stock ); Stock[Datetime] < MAX ( Stock[Datetime] ) && Stock[SKU] = SELECTEDVALUE ( Stock[SKU] ) && ( Stock[Type] = "Delivery" ) ) ) RETURN IF ( SELECTEDVALUE ( Stock[Type] ) <> "Delivery"; CALCULATE ( SUM ( Stock[Price /ea] ) * StockChange; FILTER ( ALLSELECTED ( Stock ); Stock[SKU] = SELECTEDVALUE ( Stock[SKU] ) && Stock[Datetime] = previousdatetime ) ); SUM ( Stock[Price /ea] ) * StockChange )
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |