Dynamic YoY Comparison Flag for Financial Years using Alteryx
Since I started working with data, one of the most common things I get asked is to do a year on year comparison.
The calendar year
The first step of this process is to flag the year which we are comparing (usually the current year) and the year against which we we are making the comparing ( usually the base year or last year).
As an example we want to compare the sales value from this year 2022 with those form last year 2021.
Within Alteryx we can easily do this by using a formula to identifying the current year by looking at the current date, or identifying the maximum date within a document and then comparing that to the year part of the date field within our data.
The Financial year
The issue arises when we want to compare financial years that doesn’t start in January, but instead it starts somewhere in June, or in other months. In which case the whole calculation becomes slightly more complicated because we not only need to keep in mind the year but we also need to have a look at the month as well.
Continuing with the example mentioned above, we want to compare the current financial year that ends in June with the previus financial year. For the sake of this example we are currently in June 2022, so we are still in the Financial year 2021/2022
As the first step we need to create a “Financial Year” field based on a formula that checks the month, and if this is before June (6th month) then we can say that we are still in the current year 2022. If we went past June, then we already entered the next financial year 2022/2023 so in the formula for our date flag would translate to the current year plus one.
then DateTimeYear(Date) else DateTimeYear(Date)+1 endif
The next step is identifying the latest financial year within the financial year flags assigned earlier. We’re going to use a summarise tool in which we pick the maximum of the financial year and then append it to the rest of the data.
Now all we need to do is to check whether the “Max_Financial Year” is the same as the “Financial Year”. If it is then we are looking at the “current financial year” if’s one less then we’re looking at the “previous financial year”. For everything else we’re looking at “other”
IF [Financial Year] = [Max_Financial Year] THEN "THIS YEAR"
ELSEIF [Financial Year] = [Max_Financial Year]-1 THEN "LAST YEAR"
ELSE "OTHER" ENDIF
In order to adapt this to the month in which your financial year is ending just update the “Financial Year” formula with the corresponding month number.
Hope this helps. Let me know in the comments if you ever struggled with this issue and how you managed to solve it