Forum Discussion

dbiduk's avatar
dbiduk
New Member
3 years ago
Solved

Comparing Values with Previous not based on Dates

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!

Need Help

5 Replies

  • danextian's avatar
    danextian
    Super User
    3 years ago

    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's avatar
      dbiduk
      New Member
      3 years ago

      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!!

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

    Total = SUM(HOLDINGS[value])
Chg = VAR _prev=CALCULATE([Total],TREATAS(VALUES(LOADS[previous load]),HOLDINGS[load]),REMOVEFILTERS(LOADS[load])) RETURN DIVIDE([Total]-_prev,_prev)
  • ThxAlot's avatar
    ThxAlot
    Super User
    3 years ago

    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's avatar
      dbiduk
      New Member
      3 years ago

      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.