5 Replies Latest reply on May 1, 2013 12:57 AM by Jim Wahl

    Budgets vs Actual

    Joshua Grossman

      Hello,

       

      I am running into difficulty trying to compute actual spend compared to budgeted ideal spend on a per month basis.

       

      Here is how I am currently trying to do this.

       

      First step:

       

      A) I need to figure out how to calculate % of month completed to get ideal spend. 

       

      For example if Max([Date]) of the current month is 4/15/2013 then 15/30 = 50% of the month is complete.  This represents 'ideal spend'. 

       

      I can get the last day of the month by this:

       

      DATEADD('month',1,DATETRUNC('month',[Date]))-1

       

      But I get a data type mismatch, when I try to divide Max([Date]).

       

      Second Step

       

      B) Take actual spend and divide this by a constant budget.  For example $100 actual spend divided by $1,000 budget (constant) = 10% of budget is spent.  Is it possible to create a calculated field that is a constant number that I can divide into the actual spend?

       

      Thanks for your help!

        • 1. Re: Budgets vs Actual
          Jim Wahl

          Hi Joshua,

           

          For part A, you probably want to make both of the fields integers. The DAY() function returns an integer value of the day of month for a date field.

          Max date = DAY(MAX([Date]))

          Days in Month = DAY(DATEADD('month',1,DATETRUNC('month',MAX([Date])))-1)

           

          Note that I also added a MAX() around the date in Days in month for consistency---both calculated fields need to be aggregates.

           

          I don't know if others will be maintaining this, but your Days in Month calculation was a bit difficult to understand for me initially. I might just assume a 30-day month or hard-code the number of days based on month.

           

          For part B. You might try using a parameter for your constant of $1000. Just right-click in the left-hand dimensions/measures pane > Create Parameter. You can use this parameter in a calculated field and you can also show a parameter control on the worksheet or dashboard so that it can be adjusted easily, even in an end user view.

           

          Jim

          • 2. Re: Budgets vs Actual
            Alex Kerin

            There was a thread here for looking at number of days in a month: http://community.tableau.com/message/192929

            • 3. Re: Budgets vs Actual
              Joshua Grossman

              This worked!  Thanks for your help, Jim.

               

              One more question:

               

              How can I find the number of days past in a quarter.  I know the function 'dayofyear', is there a 'dayofquarter' or equivalent?

               

              Thanks!

              • 4. Re: Budgets vs Actual
                Jim Wahl

                Hi Joshua,

                 

                Glad it worked. Here's how I might do "Day of quarter"

                CASE DATEPART('quarter', [Date])

                WHEN 1 THEN DATEDIFF('day', DATE("1/1/"+STR(YEAR([Date]))), [Date])

                WHEN 2 THEN DATEDIFF('day', DATE("4/1/"+STR(YEAR([Date]))), [Date])

                WHEN 3 THEN DATEDIFF('day', DATE("7/1/"+STR(YEAR([Date]))), [Date])

                WHEN 4 THEN DATEDIFF('day', DATE("10/1/"+STR(YEAR([Date]))),[Date])

                END

                 

                Alex's link also might be useful to you, as it seems they've covered some similar issues in probably a lot more detail (Alex and Shawn both have deep experience / expertise with Tableau). ...

                 

                Jim

                • 5. Re: Budgets vs Actual
                  Jim Wahl

                  An even easier formula. ...

                  Day in quarter = DATEDIFF('day', DATETRUNC('quarter', [Date]), [Date])