Comparing Values with Previous not based on Dates

[dbiduk]

Hi,

Relative noobie here.  I have checked many existing posts and videos but I am not getting how to approach the problem.

I have 2 tables as below, with one-to-many from LOADS.load to HOLDINGS.load in order to define the Previous Load given a Load.

And this is what I would like to see in a Matrix or Table:

I realize I need to do something at the Query level (new column or some DAX expression) but I don't understand how I can add a column to my Holdings table that says something like:

Previous Value = HOLDINGS.Value where current.Load = LOADS.Previous Load and current.Holding = HOLDINGS.Holding

And then calculating the Chg would be easy enough (Calculated column).

Please help!

[ThxAlot · Accepted solution]

Previous period value.pbix

Simple enough if the rule of "previous load" persists; even the LOADS table is redundant. (rule of "previous load": the max Load less than current Load)

[dbiduk]

Even though all solutions posted work, I like this one because I don't have to create the Load table and entries - even though I didn't ask for this.

Thanks all for helping me out.

[wdx223_Daniel]

Total = SUM(HOLDINGS[value])
Chg = VAR _prev=CALCULATE([Total],TREATAS(VALUES(LOADS[previous load]),HOLDINGS[load]),REMOVEFILTERS(LOADS[load])) RETURN DIVIDE([Total]-_prev,_prev)

[danextian]

Hi @dbiduk ,

With the given data, create two calculated columns

Previous Load by holding = 
CALCULATE (
    MAX ( 'Table'[Load] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Holding] ),
        'Table'[Load] < EARLIER ( 'Table'[Load] )
    )
)

Previous value by holding = 
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Holding] ),
        'Table'[Load] = EARLIER ( 'Table'[Previous Load by holding] )
    )
)

Use the second column to create a % change measure.

Note: the look you want  for your matrix is not currently achievable out of the box ( will require advanced DAX)

Please see attached pbix for reference.

[dbiduk]

Actually, this one doesn't need the LOAD table either.  I kinda prefer this one becuase it adds the new data as calculated columns, not measures.  But, again, I am new so what do I know :-).

Thanks!!