none
Using a proxy month for an analysis

    Question

  • This was cross-posted on another forum; it won't let me post a link until they verify my account.  It was on the MrExcel PowerPivot message board under the same thread name.  Apologies.

    I have a pivot with several linked tables. 
    Table Job contains:
    Job Code (unique identifier)
    CloseDate (the date the job was sold)
    Month1 (Financial period 1 month after job was sold)
    Month2 (Financial period 2 months after job was sold)
    ... (3-11)
    Month12 (Financial period 3 months after job was sold)

    Table JobCostDetail contains:
    Amount
    Financial Period
    Job Code (this links to Job Code in Table Job)

    I'm trying to put Months 1-12 as column headers and get values for lagging amounts spent in the twelve months after a job is sold. Any ideas?

    I can think of how I'd do it in Excel native, but the type of analysis I'm performing really lends itself to PowerPivot as it involves many linked tables and I'm trying to avoid using lookups as a crutch.

    The plain English for what I'm trying to determine is "The sum of Amount for Job X for MonthX after sale". Months 1-12 would be the columns.  It seems that a measure would be best.

    I tried to write a measure into table Job for just month 1, to see if I could get it to recognize what was going on.  The syntax I used is as follows:

    =calculate(sum(JobCostDetail[Amount]),JobCostDetail[FinancialPeriod]=[month1]

    I got the following error message: "The value for 'month1' cannot be determined. Either 'month1' doesn't exist, or there is no current row for a column named 'month1'."

    Month1 is a variable, depending on when the job was sold.

    • Edited by PivotEverything Wednesday, April 24, 2013 9:59 PM Cross-posting notification
    Wednesday, April 24, 2013 9:54 PM

Answers

  • Your basic issue is caused by the fact that CALCULATE will not directly allow comparison to a measure as the second argument. To achieve this you need to put the condition a FILTER() function. This is a great post by Rob Collie on the subject.

    Although you could rewrite your measure using this technique I would recommend looking at changing your approach as you are going to have to write 12 measures when really if you make a few changes, it can be a single one. I would approach the issue by adding a calculated column to the 'JobCostDetail' table that for each line gives the months elapsed since Close Date. Assuming you introduce a date table that has columns for at least date and financial period and that 'JobCostDetail' is related to Job then you could use a formula like:

    =[Financial Period]-
          LOOKUPVALUE(
                      DimDate[Financial Period],
                      DimDate[Date],RELATED(Job[Close Date])
                      )
     

    This is likely a simplification as to span multiple years will require an extra step, possibly with the use of a 'Financial Period Serial' which would be unique ascending numbers in a column in the date table used purely for calculations.

    Hope this gives you an idea.

    Jacob

    Thursday, April 25, 2013 10:39 AM

All replies

  • Your basic issue is caused by the fact that CALCULATE will not directly allow comparison to a measure as the second argument. To achieve this you need to put the condition a FILTER() function. This is a great post by Rob Collie on the subject.

    Although you could rewrite your measure using this technique I would recommend looking at changing your approach as you are going to have to write 12 measures when really if you make a few changes, it can be a single one. I would approach the issue by adding a calculated column to the 'JobCostDetail' table that for each line gives the months elapsed since Close Date. Assuming you introduce a date table that has columns for at least date and financial period and that 'JobCostDetail' is related to Job then you could use a formula like:

    =[Financial Period]-
          LOOKUPVALUE(
                      DimDate[Financial Period],
                      DimDate[Date],RELATED(Job[Close Date])
                      )
     

    This is likely a simplification as to span multiple years will require an extra step, possibly with the use of a 'Financial Period Serial' which would be unique ascending numbers in a column in the date table used purely for calculations.

    Hope this gives you an idea.

    Jacob

    Thursday, April 25, 2013 10:39 AM
  • Jacob,

    Once again, you've hit it on the head.  I made one change to your formula, which was to add months.  The final calculated column was as follows:

    =month([FinancialPeriod])-

    month(LOOKUPVALUE(

    DimDate[FinancialPeriod],

    dimdate[date],related(Job[ActualCloseDate]))

    )

    Thank you very much for your time on this. I'm still having to translate with DAX, rather than just thinking in it. I really appreciate the help.
    Thursday, April 25, 2013 1:32 PM