Identify Previous value based on two columns - column needed

[apowerbiguy]

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!

[MFelix]

Hi @apowerbiguy ,

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.

[apowerbiguy]

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:

BU	Fleet	Avail	Date
A	X	xvalue	10/1/2020
A	Y		10/1/2020
A	X		10/1/2021
A	Y		10/1/2021
B	X
B	Y
B	X
B	Y

Thank you again for replying

[v-rzhou-msft]

Hi @apowerbiguy ,

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.

[MFelix]

Hi @apowerbiguy ,

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.

[apowerbiguy]

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]

Hi  @apowerbiguy ,

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