3 Replies Latest reply on Feb 9, 2017 1:49 PM by Jim Dehner

    Calculating prior month when crossing calendar years

    Rebecca Smith

      I have a workbook that contains company wide metrics that is setup to provide the current and prior month numbers based on file date parameter that is set for the month I choose. I have an issue calculating prior month when crossing calendar years. Nothing populates for the prior month, December 2016, now that the current month is January 2017. We're using an annualized calculation.

       

      For the current month -

      ((sum([Total Promotions & Transfers Current]))/([HEADCOUNT DATA].[Average Headcount Current]))/(MONTH([File Date Parameter]))*12

       

      For the prior month -

      ((sum([Total Promotions & Transfers Prior])/[HEADCOUNT DATA].[Average Headcount Prior])/(MONTH([File Date Parameter])-1))*12

       

      I know this issue is tied to the use of month for the file date parameter, but I haven't been able to successfully include year as part of the calculation.

       

      I am not able to share a packaged workbook as the data included is too sensitive. I'm hoping the calculation will be enough to garner a response since I'm looking to find a way to point the data in the prior year when the current month is January 2017 and prior month is December 2016.

        • 1. Re: Calculating prior month when crossing calendar years
          Jim Dehner

          Hi Rebecca

           

          I had to deal with something similar and found the only way around was to check the month and the year with and IF / And statement

           

           

          if [Period]='m'

          and

          DATEPART('month',[date])=

          datepart('month',[date parameter] )

          and

          DATEPART('year',[date])=

          datepart('year', [date parameter])

          then[Sales]

          end

           

          Not sure what else you have in your dimensions that you can check year against

           

          Hope it helps - even a little

           

          Jim

          1 of 1 people found this helpful
          • 2. Re: Calculating prior month when crossing calendar years
            Joe Oppelt

            To share sensitive data, anonymize your data.  See this 10 minute video that shows how.  We may need to see a workbook at some point, so I recommend that you start working on this:

             

            Anonymize your Tableau Package Data for Sharing

             

            Where are you generating your [... current ...] and [... prior...] fields from?

             

            You can just store your data with date values in the rows, and Tableau's magical date handling can do the math for you.  And tableau doesn't care about year boundaries.  So there are myriad ways you can get last month's data right from the data as it comes in from your data source.

             

            For instance, DATEADD('month',-1,[date field]) will subtract a month.  Doesn't matter if it is July or January.  Doesn't matter if the field is a fiscal date or calendar date.

             

            If your sheet is set up with monthly cells, LOOKUP(<whatever>, -1) will grab <whatever> from the prior month.  Again, no problem with year boundaries.

            2 of 2 people found this helpful
            • 3. Re: Calculating prior month when crossing calendar years
              Jim Dehner

              Thanks Joe - that would have been much easier than what I did

              Jim