7 Replies Latest reply on Apr 17, 2018 2:02 PM by Deepak Rai

# Display Current and Prior Year Revenue by Month

Hi,

I feel like this is a fairly common question, but I am struggling mightily in coming up with the solution that I am hoping to get your help on...

I am building out a dashboard that displays NET REVENUE, broken out by the dimension MONTH YEAR. Here is a sample set for how that data is displayed:

 Month Year Net Revenue Jan-16 10 Feb-16 15 Mar-16 20 Apr-16 25 May-16 30 Jun-16 35 Jul-16 40 Aug-16 45 Sep-16 50 Oct-16 55 Nov-16 60 Dec-16 65 Jan-17 10 Feb-17 20 Mar-17 30 Apr-17 40 May-17 50 Jun-17 60 Jul-17 70 Aug-17 80 Sep-17 90 Oct-17 100 Nov-17 110 Dec-17 120 Jan-18 20 Feb-18 18 Mar-18 16 Apr-18 14 May-18 0 Jun-18 0 Jul-18 0 Aug-18 0 Sep-18 0 Oct-18 0 Nov-18 0 Dec-18 0

Within the Dashboard, each month is displayed from January to December (meaning it's not a last-12-months view, but a current view for each month in a given year). There is a filter for YEAR. When you set the filter to YEAR = 2018, there are 12 data columns displayed from JAN through DEC that displays the net revenue for that specific year.

so, if I set YEAR = 2018, the data display should be as follows:

 JAN FEB MAR APR MAY JUNE JULY AUG SEPT OCT NOV DEC Net Revenue 20 18 16 14 0 0 0 0 0 0 0 0 Prior Net Revenue 10 20 30 40 50 60 70 80 90 100 110 120

Note that when I select YEAR = 2018, that "Net Revenue" pulls the net revenue for each month in 2018, whereas "Prior Net Revenue" is pulling the net revenue for each month in 2017.

Likewise, if you set YEAR = 2017, the display should be like this:

 JAN FEB MAR APR MAY JUNE JULY AUG SEPT OCT NOV DEC Net Revenue 10 20 30 40 50 60 70 80 90 100 110 120 Prior Net Revenue 10 15 20 25 30 35 40 45 50 55 60 65

I have figured out how to pull the "Net Revenue" row, but cannot pull the "Prior Net Revenue" accurately. Is there a quick solution for this?

Thank you!

• ###### 1. Re: Display Current and Prior Year Revenue by Month

Hi Ken,

Have a look Here, You need a parameter and a calculated field to do that. Please see attached and Screenshots.

Thanks

Deepak

If This Helps, Pl mark it Helpful and CORRECT to close Thread

For 2018

For 2017

• ###### 2. Re: Display Current and Prior Year Revenue by Month

Hi Deepak,

Your workbook example is showing blank worksheets for me, and I think it

has something to do with the Date dimension that you created... I tried

using that exact formula but Tableau is giving me an error.

Can you double-check on your Date parameter and let me know that is

correct? I could be doing something wrong with the data as well...

Thank you,

Ken

On Tue, Apr 17, 2018 at 12:33 PM, Deepak Rai <tableaucommunity@tableau.com>

• ###### 3. Re: Display Current and Prior Year Revenue by Month

Your date was in different format so I created another Calc to add The Day to it and thus it worked fine. What is your Datasource?

• ###### 4. Re: Display Current and Prior Year Revenue by Month

The main issue is that the calculated date fields in your workbook example

provide the incorrect date. I think it is syntax related. See below for

screenshot:

It adjusts the date to be 1/16/2001, when the intention is obviously to

make that 01/01/2016. I think if this can be adjusted, then the solution

should work.

My datasource is a much larger, complicated data set out of Redshift. But

for the sake of this exercise those are the two fields that are of concern.

thank you very much for your assistance with this!!

On Tue, Apr 17, 2018 at 1:46 PM, Deepak Rai <tableaucommunity@tableau.com>

• ###### 5. Re: Display Current and Prior Year Revenue by Month

I don't know but it is CORRECT at my end. and That's why my results match with what you posted

• ###### 6. Re: Display Current and Prior Year Revenue by Month

Haha I figured you probably did something write to get the correct solution!

I have to adjust the date code on my end to make it work, but the solution

should work at that point. Thank you for your time and assistance on this!

On Tue, Apr 17, 2018 at 1:56 PM, Deepak Rai <tableaucommunity@tableau.com>