Is there a way to build a metric that calculates a static quarter, i.e. always looks at Q1 no matter what month you're in, instead of looking at current month or month-1?  I have a feeling that there is, but I'm wondering if my specific use case that I need this for is complicating it for me.

I have a number of different Budget outlooks that I load throughout the year, and I want to compare them all in one table view (see example below).  They are loaded into separate raw tables, and then consolidated into one transform table.  However, (and I think this might be the complicating factor), they are based on time in Apptio, so when you're looking at the transform in Jan FY2015, you are only seeing the January numbers for each version.  I can build a metric easily enough that shows me the current quarter's total, or any other quarter's total relative to the month that I am in.  But what I can't figure out how to do is build a metric that will always show me Q1's total regardless of the month I'm in in Apptio.  Any ideas?

Two ideas:

1. Change data time period (report table properties).

2. LookupFromPath() function.

Details:

1. Change data time period (report table properties).

A report table can be locked to a different time period (or date range) other than the month selected near top center of the screen. Right-click a table > Properties > Advanced, to see:

...the Data Time Period field can be changed from Current Project Date to another option (current fiscal quarter, start of project time, start of quarter, etc.). But if you use this option to lock a report table to Entire Current Fiscal Quarter, it will only show Q1 if the user has selected one of the months within Q1--so it doesn't quite meet your described need.

2. LookupFromPath() function.

This is one of the few functions that lets you specify an exact time period to fetch table data from.

It's possible to combine LookupFromPath() with CurrentDate() to identify the currently selected fiscal year and then forcibly fetch data from just the three Q1 months of that year.

From your description it sounds as though you're using table data and not metrics to compile and view budget details. If so, this strategy should work.

Thanks, Christopher.  I'm trying to play around with the second option, which seems like it would give me what I'm looking for.  A couple of questions... would this just be a synthetic column that I would add to a reporting table and then enter the LookupFromPath function in the formula?  Or would I need to build a metric that does this and then pull that into the report?

I've tried making a synthetic column, but either that's not the right way to do it, or I've got something wrong in the syntax.  What I have is:

=LookupFromPath(DomainName()&":"&ProjectName()&"/Data/"&CurrentDate("yyyy")&"/Budget Template",Budget Template.Current Date,IN("1","2","3"),Budget Template.Value)

The idea being... look at the Budget Template table across all periods in the current year and sum the Value column where the Current Date field is 1, 2, or 3.  But I'm getting an error:  !ERR: Encountered " "IN" "IN "" at line 1, column 125. Was expecting one of: <DELIMITED_IDENTIFIER> ... <IDENTIFIER> ...

You're right--the idea is to create one or more columns in a data table (or a report table, although I recommend a data table). No need to build a metric.

I am not sure though if the IN function can be directly inserted into the arguments of the LookupFromPath() function, as shown in your example above.

Getting your specific example working (Q1 budget from multiple tables or a combined table) might entail spreading out into more than table column, i.e. results of one column depend on another column's results.

I feel like I'm on the right track with this, but I'm not quite there yet.

I've taken your advice and tried to build a new column in the Budget Template data table rather than in the report table.  The column value is set up as:

=LookupFromPath(DomainName()&":"&ProjectName()&"/Data/"&CurrentDate("yyyy")&"/Budget Template",Budget Template.Version Quarter,Budget Template.Version Quarter,Budget Template.Value)

The Version Quarter field is populated with values like "Plan - 1" indicating that the version is Plan and the quarter is 1 (quarter is 1, of course, wherever month is Jan, Feb, or Mar).  My thought is that this would sum up all the values across all of 2015 where this Version Quarter field is the same.  But it doesn't seem to be doing that.  It doesn't seem to do anything actually, except kill my performance when trying to look at this table.  Perhaps it's my understanding of what the LookupFromPath function is actually doing?  It seems somewhat similar to the SumIf function (and that it how I am trying to us it), except that instead of just summing the values in the table in the current month, it should sum up the values in all months across the entire current year.

I notice you're using just the year in the function, which is OK--it just means your returned values will all be from the first month of your fiscal year.

Also, you're including the table name before the column name--the LookupFromPath() function doesn't need the table name included. Actually, I'm not sure it will work with the table name included.

Perhaps try:

=LookupFromPath(DomainName()&":"&ProjectName()&"/Data/"&CurrentDate("yyyy")&"/Budget Template",Version Quarter,Version Quarter,Value)

And, you're using LookupFromPath() to perform a lookup from a data table to itself. Is there any way you can separate out this functionality, such that LookupFromPath() is being used to look up values from a different data table? I haven't seen a self-referential LookupFromPath() before, and it might be causing the performance hiccup you mentioned.

So, what do I need to use to return values from all months in the fiscal year?  I thought just specifying the year would do that.

The self-referential process does seem to be what's causing the performance hiccup.  I've tried separating out the functionality to look up into a different table, but still haven't gotten it to work.

=LookupFromPath(DomainName()&":"&ProjectName()&"/Data/"&CurrentDate("yyyy")&"/Budget Template",Version Quarter,Budget Template Summary Transform.Version Quarter,Budget Template Summary Transform.Value)

Budget Template Summary Transform is the table that is being looked up.  As I understand the function, it should be saying, take the value in the Version Quarter field in the Budget Template table and look for it in the Budget Template Summary Transform table, then sum up the Value field in the Budget Template Summary Transform table.

Your approach makes sense, although you still may want to try removing the table names from the function:

=LookupFromPath(DomainName()&":"&ProjectName()&"/Data/"&CurrentDate("yyyy")&"/Budget Template Summary Transform",Version Quarter,Version Quarter,Value)

The name of the lookup table is part of the function's first argument, so it doesn't need to be repeated in the third and fourth arguments. (Similar syntax-wise to the more common Lookup() function).

Also, a single LookupFromPath() function can only look at a single time period, not a range of time periods (and, unfortunately, not an entire fiscal year all at once).

But it would be possible to creatively construct a long formula which sums results from two or more LookupFromPath() functions, each one looking at a specific month.

For example:

=LookupFromPath(DomainName()&":"&ProjectName()&"/Data/"&"January:2015"&"/Budget Template Summary Transform",Version Quarter,Version Quarter,Value)+LookupFromPath(DomainName()&":"&ProjectName()&"/Data/"&"February:2015"&"/Budget Template Summary Transform",Version Quarter,Version Quarter,Value)+LookupFromPath(DomainName()&":"&ProjectName()&"/Data/"&"March:2015"&"/Budget Template Summary Transform",Version Quarter,Version Quarter,Value)

Or a version which auto-adjusts to the currently selected year but stays in Q1 (assuming Q1 is Jan/Feb/Mar):

=LookupFromPath(DomainName()&":"&ProjectName()&"/Data/"&"January:"&CurrentDate("yyyy")&"/Budget Template Summary Transform",Version Quarter,Version Quarter,Value)+LookupFromPath(DomainName()&":"&ProjectName()&"/Data/"&"February:"&CurrentDate("yyyy")&"/Budget Template Summary Transform",Version Quarter,Version Quarter,Value)+LookupFromPath(DomainName()&":"&ProjectName()&"/Data/"&"March:"&CurrentDate("yyyy")&"/Budget Template Summary Transform",Version Quarter,Version Quarter,Value)

This is very close to working.  I've entered the "January:"&CurrentDate("yyyy") version into my table field, and it works in February and March, but not in January.  Maybe because it's looking at the same table in the same month?

If you're performing the LookupFromPath() from one table to itself (instead of to a separate table), then you're right, maybe the self-reference is causing a problem.

I ultimately ended up coming up with a different solution for this issue.  It's perhaps a little clunkier than the LookupFromPath option, but I was unable to overcome the issue of using that function to add values across time while looking at the same table (creating a self-reference).  To get this working, I changed my transform table to not be date filtered, so all months' data showed up in every month of the year.  Then I just created more synthetic columns to add up the values into quarters that could be displayed on my report.  Kind of a brute force method, but it works, so that's the important thing.

James: if you are date column filtering, look at datesum [unfortunately, it won't help with row date filtering].

A couple other things that you may want to look at depending on your exact usecase:

1) if you are trying to lock a signifiant portion of a report component to a particular time period, then you may be able to do this with ad-hoc query,  under the 'Time' perspective, pick the +- simple next to 'Quarters' and drag '1st Quarter' into your report component

2) If you need this for a formula in a table transform or a model, then the approach you are already taking is basically what you need to do. If it's a formula in a model, I would concatenate together a unique key, make a transform of your non-time filtered table, and time filter that. They'll inference link on your unique key, so you can still has your allocations weight by the monthly values.

3) if you need it on a report, but need to be able to reference it from other formulas, for example to do variance reporting, then you can get it with this formula on the ribbon:

=eval("=quarter(Cost,-"&trunc((Value(currentDate("M"))-1)/3)&")")

Note: This formula assumes for fiscal year starts in January, and that you are on a 12 period fiscal year. This part of the formula: (Value(currentDate("M"))-1) can be tweaked for 13 period, and the offset changed from 1 for fiscal years other than January if needed. This formula figures out how many quarters ago 'q1' was. It then leverages eval since the quarter function does not let you nest a sub formula into it's second argument. I should also mention, pulling up aggregated time periods [which the quarter function will do] can sometimes be fairly expensive depending on your model.

Important Note on option #3: your query must have one or more published perspectives in it that has the checkbox 'Show All Rows in Time Based Query' checked. This will ensure rows that exist in other months of the fiscal year show up. Option 3 will only work for Q1 of the current fiscal year, as show all rows in time based query adds rows from the current year, but not prior years.