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 HOLDIN...
Need Help
  • ThxAlot's avatar
    ThxAlot
    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)

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