Dynamic Baseline Value Compared to Values in Timeframe and SUM

[ksoukup]

Hello Experts :),

My dataset is purchase history for parts coming into the warehouse. I need to be able to identify a "baseline" cost price at a supplier+part number combination and compare that value to the cost price from a different timeframe, to see where we may be seeing increased or decreased costs. The "baseline" cost price would be the last cost price from the baseline timeframe. There is a field in the dataset called "rank_newest" that ranks the transactions within the supplier+part number combination newest-to-oldest.

What I wish to accomplish is to give the users one date slider to identify the "baseline" timeframe and a separate date slider to identify the "review" timeframe. I am so very close with this one!

I start with two date tables. The active relationship is for the "review" timeframe and the inactive relationship is for the "baseline" timeframe.

The user can adjust the date sliders to their desired baseline and review timeframes.

Here is a quick rundown of the measures. I put them in numerical order.

The first measure will identify the MIN rank_newest from the baseline timeframe; this will be the last order placed in the baseline timeframe.

1min rank in baseline =

CALCULATE (

    MIN ( data[rank newest] ),

    ALLEXCEPT (

        data,

        data[Concat Supplier + PN]

    ),

    USERELATIONSHIP (  'data'[Order Date], 'Date Table - disconnected'[Date] )

)

The second measure will use the first measure to return the cost price from the MIN rank newest from the first measure.

2cost from min rank in baseline =

CALCULATE (

    MIN ( data[Cost per 100] ),

    FILTER(

    ALLEXCEPT (

        data,

        data[Concat Supplier + PN]

        --data[Cost per 100]

    ), 'data'[rank newest] = [1min rank in baseline]),

    USERELATIONSHIP (  'data'[Order Date], 'Date Table - disconnected'[Date] )

)

The third and fourth measures simply compare the baseline cost price to each individual cost price within the "review" timeframe and then calculate the extended cost of those differences based on the order quantity.

In the example below, the "baseline" cost price is the last cost price from the baseline timeframe-- $12.96. I then compare that baseline cost price to the cost price of each transaction in the "review" timeframe. The issue I have is that I can't figure out how to SUM the result of '4 diff extended'. Being able to sum would allow tables to evaluate performance by part numbers, suppliers, buyers, etc.

This last image would be the desired outcome. I would like to be able to do this across all of the Supplier+Part Number combinations simultaneously to arrive at a total dollar value of cost savings or cost increases.

I have tried using SUMMARIZE to do this without any luck!

Here is a link to the pbix with sample data:

https://www.dropbox.com/s/k5px5rvb5m027gl/Example%20Power%20BI%20File.pbix?dl=0 

Any help is greatly appreciated!!

-Kyle

[ksoukup · Accepted solution]

I had a "duh" moment! I've updated the measure '4 diff extended' to use SUMX. This is giving me the correct result at the part level and at the total level. I also had to add an IF statement so it wouldn't calculate when there was no cost available from the baseline timeframe.

Updated measure:

4 diff extended =

SUMX(

    'data',

    ([order qty] * IF([2cost from min rank in baseline] = BLANK(), 0, [3 diff in cost from baseline])) / 100

)

Cheers!