9 Replies Latest reply on Apr 25, 2018 9:39 AM by Hari Ankem

# Last Year Calculation (Different Fiscal Year)

Dear all,

In my company, the fiscal calendar is very different. Knowing this, a calendar table was created in the database, where I have:

Column and Example

• Revenue = 300,000.00
• Order Date = 20180420
• CA Year ID = 2018
• CA Semester ID = 20181
• CA Quarter ID = 20181
• CA Month ID = 201802
• CA Week ID = 201807
• CA Day of Month ID = 19
• CA Day of Week ID = 5
• LY Order Date = 20170421
• CA LY Year ID = 2017
• CA LY Semester ID = 20171
• CA LY Quarter ID = 20171
• CA LY Month ID = 201702
• CA LY Week ID = 201707
• CA LY Day of Month ID = 19
• CA LY Day of Week ID = 5

I know that in 20170421 (last year) the Revenue was 250,000.00. But, I dont know how to create the field Revenue LY to compare the values.

How can I do this?

• ###### 1. Re: Last Year Calculation (Different Fiscal Year)

1. Join the data with itself as shown below. (Optional: Rename the object names to CY and LY so that they are easy to identify.)

2. You should now have the data as needed.

Hope this helps. A 10.5 workbook is attached.

• ###### 2. Re: Last Year Calculation (Different Fiscal Year)

Hello @Hari Ankem,

Today, I cant do this method. My base is separated by orders x ordered items, I need to find some way of calculating within the table itself.

• ###### 3. Re: Last Year Calculation (Different Fiscal Year)

I didn't understand this statement: "My base is separated by orders x ordered items". Please clarify with some data.

• ###### 4. Re: Last Year Calculation (Different Fiscal Year)

Sure!

It's like this:

ORDER: w01-0001

Item: A

Revenue: 50

ORDER: w01-0001

Item: B

Revenue: 45

ORDER: w01-001

Item: C

Revenue: 30

• ###### 5. Re: Last Year Calculation (Different Fiscal Year)

So, basically the revenue is at an order-item level. So, do you need to compare the revenue YOY for each order and/or item combination? Will the order number be the same across different years? If so, you can always include the order and/or item too to the join condition shown above. If not, please explain what your needs are.

• ###### 6. Re: Last Year Calculation (Different Fiscal Year)

Hello Hari,

Yes, I need to compare the revenue YOY for each DAY, WEEK, MONTH (data nivel). The importance of order-item level is to show the data in all different categories here. The order number is not the same across the years (its different).

• ###### 7. Re: Last Year Calculation (Different Fiscal Year)

OK. Hope this helps.

I have done a Union of the data 4 times, once each for YEAR, MONTH, WEEK and DAY. This data is then joined with another Union of the same data 4 times. I have used a calculated join. You can include an additional join for an Item as required.

\

I am using the following LOD calculations:

I have a parameter to select the period and have an additional filter to ensure the data join is correct. (I did observe some incorrect data being joined, and could not figure that out for now. So, had to use the Data Join filter as shown below).

You now have the required outputs:

You can probably achieve the above functionality using Custom SQL on Excel files on Windows systems. Since, I am on a Mac, this ended up being slightly complex.

• ###### 8. Re: Last Year Calculation (Different Fiscal Year)

Thanks Hari,

Very nice alternative.

But I have some problems:

-Orders are from different categories, I can create some primary key to make the correct join;

-My data source is connected to Tableau Server and because of this I cant make joins...

Is there any formula-only method?

• ###### 9. Re: Last Year Calculation (Different Fiscal Year)

Are you using any database? If yes, you may want to check if it supports analytic/window functions. Then you may hopefully be able to modify your queries and get the desired solution.

As for a formula-only method, I can explore further but cannot guarantee a solution right away.