Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
simonfalun
Frequent Visitor

Get price from last delivery, per article

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:

help1a.jpg

And this is what I want to end up with:

help1b.jpg

1 ACCEPTED SOLUTION
simonfalun
Frequent Visitor

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!

View solution in original post

3 REPLIES 3
simonfalun
Frequent Visitor

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!

v-yulgu-msft
Employee
Employee

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] )
    )

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
    )

 

help2.jpg

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.