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 ,
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.