Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Identify Previous value based on two columns - column needed

Good MorningEverybody,

 

I am hoping that you can assist with a calculation error I am having. I am attempting to create a new column which will show 

the Availability value from 12 months prior (same month previous year).  This table has multiple items in one table and it would need to look up the previous value while ensuring that the BU field and the FLEET column values are the same. I do have a date table which is called Calendar.
 
Does anybody have any ideas on how to do this? I've done this in other tables but never where multiple items are in the same table.  
 
Thank you for the help in advance!
 

 

Need Help

6 Replies

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    Hi Anonymous ,

     

    Try the following syntax:

    PY Availability = 
CALCULATE (
    SELECTEDVALUE ( 'Availability'[Availability] ),
    FILTER (
        ALL ( 'Availability' ),
        'Availability'[Year]
            = EARLIER ( 'Availability'[Year] ) - 1
            && 'Availability'[Quarter] = EARLIER ( 'Availability'[Quarter] )
            && 'Availability'[BU] = EARLIER ( 'Availability'[BU] )
    )
)

     

    Has you can see the result brings previous year values:

    I used the quarter column because it appears that you don't have a monthly value but a quartely value.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello MFelix,

       

      Thank you so much for replying; however, this will not work because it needs to be the previous year for the same "BU" value and same "Fleet" value. There is a Date Field named "Date" (month was just that we track them monthly) and would need to be the previous year date which is listed as 10/1/2020, 10/1/2021. 

       

      For instance:

       

      BUFleetAvailDate
      AXxvalue10/1/2020
      AY 10/1/2020
      AX 10/1/2021
      AY 10/1/2021
      BX  
      BY  
      BX  
      BY  

       

       

      Thank you again for replying

      • MFelix's avatar
        MFelix
        Super User
        4 years ago

        Hi Anonymous ,

         

        You are correct about the Fleet I forgot about it on my calculation regardung the date try the following measure:

         

        PY Availability = 
CALCULATE (
    SELECTEDVALUE ( 'Availability'[Availability] ),
    FILTER (
        ALL ( 'Availability' ),
        'Availability'[Year]
            = EARLIER ( 'Availability'[Year] ) - 1
            && 'Availability'[Date] = EARLIER ( DATEADD('Availability'[Date] ), -1 , YEAR))
            && 'Availability'[BU] = EARLIER ( 'Availability'[BU] )
            && 'Availability'[Fleet] = EARLIER ( 'Availability'[Fleet] )
    )
)

         

        Did this by heart if it gives an error should be on the DATEADD, please tell me.