Forum Discussion
Identify Previous value based on two columns - column needed
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
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.
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
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] ) ) )