6 Replies Latest reply on Oct 3, 2017 12:16 PM by Nandhakumar Ramanathan

# Last 12 Month Measure Calculation for a given month

Hello Tableau Community,

I'm new to Tableau and I have difficulty in calculating last 12 months sales for a given Order Date Month.

Attached is the workbook, I'm pulling last 6 months of Order Date and I want my sheet to display Sales for given Month of Order Date and Last 12 Months and Previous Year Last 12 Months.

I created couple of Calculated fields to pull the required data and I can understand why they're not working correctly but I can't figure out how to make them return correct information.

For Example:

If Month(OrderDate) is November 2016,

(1) I want SUM(Sales) for November 2016

(2) I want SUM(Sales) from Dec-2015 to Nov 2016

(3) I want SUM(Sales) from Dec 2014 to Nov 2015

This should all display on single row for last 6 Months.

Thanks

• ###### 1. Re: Last 12 Month Measure Calculation for a given month

Hi Maneesh

Yes, it's little bit tricky to understand data filter and display filter.

The method you used is filtering data itself and you can not calculate past data.

Instead, you need to use table calculation with index formula to filter at Display.

To filter display for the latest 6 month, you can use first.

[Date index]

first()

And filter only 0-6 with the sort of "Order date" as "desc" order.

Now you could filter only display.

Then sum calculation also needs to be modified to calculate "Relative 12 months"

[Last 12 Months Sales SM]

window_sum(sum([Sales]),0,11)

[Previous Year Last 12 Months Sales SM]

window_sum(sum([Sales]),12,23)

Thanks,

Shin

• ###### 2. Re: Last 12 Month Measure Calculation for a given month

Thank you Shinichiro, worked like a charm.

• ###### 3. Re: Last 12 Month Measure Calculation for a given month

You are very welcome.

Thanks,.

Shin

• ###### 4. Re: Last 12 Month Measure Calculation for a given month

Hi Shinichiro,

Can you help me here?

Please let me know how to achieve this.

when i select filter value it should show previous 12 month values.

For eg., in the below image i have selected June'17 so the chart should show Jul'16 to Jun'17.

Thanks & Regards,

Nandha

• ###### 5. Re: Last 12 Month Measure Calculation for a given month

Hi Nandhakumar

Yes, I am willing to help.

However, for fresh start, could you post other thread with attaching packaged workbook?

Thanks,

Shin

• ###### 6. Re: Last 12 Month Measure Calculation for a given month

Already I have a fresh start in the name of "Rolling 12 months from selected Year/ Month filter" can you please proceed with it.

Thanks & Regards,

Nandha