Forum Discussion
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 ...
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.