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 ,

 

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] )
    )
)