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 ...
Need Help
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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi,

       

      Sorry but the year column can't be used. The only columns that would be there is what is the table seen in the reply Fleet, BU, Avail, and Date.  

       

      My apologies 

      • MFelix's avatar
        MFelix
        Super User
        4 years ago

        Hi  Anonymous ,

         

        You can remove the Year from the sintax.

         

        PY Availability = 
CALCULATE (
    SELECTEDVALUE ( 'Availability'[Availability] ),
    FILTER (
        ALL ( 'Availability' ),
            'Availability'[Date] = EARLIER ( DATEADD('Availability'[Date] ), -1 , YEAR))
            && 'Availability'[BU] = EARLIER ( 'Availability'[BU] )
            && 'Availability'[Fleet] = EARLIER ( 'Availability'[Fleet] )
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    As far as I know, DATEADD function will return a table and EARLIER function only support a column. So there should be something wrong in  MFelix's code.

    Try this code:

    Availability Same day in Previous Year = 
CALCULATE (
    MAX ( 'TableName'[Availability] ),/* IF [Availability] is in number format, try SUM function.*/
    FILTER (
        ALLEXCEPT ( TableName, TableName[BU], TableName[Fleet] ),
        'TableName'[Date]
            = DATE ( YEAR ( EARLIER ( TableName[Date] ) ) - 1, MONTH ( EARLIER ( TableName[Date] ) ), DAY ( EARLIER ( TableName[Date] )  ) )
    )
)

    Result is as below.

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.