We're working on creating a financial month end close report to help our finance colleagues out with their month end close process. The data from this report is coming from a SQL query that shows prior month costs for the year. As an example, when closing February, it would show February costs even though we are technically in March. 

We are looking to report costs not only for the prior month but also for YTD. 

At first I had a date partition in the transform pipeline however realized that by creating a YTD metric we would not capture all transactions when putting in a date partition. Within the query there is a month column. 

I've removed the date partition now and wondering if there's a way to report just the prior month's cost along with YTD data?   As an example just reporting March data in April but also YTD through April. 

Hi,

If I am understanding your questions correctly yes this can be done. You would use the metric associated to the amounts in the table and then use the time option in the ad hoc query to pull in the time frames that you want. If you do not have a Metric associated to the amounts you will have to create one as I do not think you can just do it with a column/formula column (You can always try that first). See below for screenshot of my test I did with my dummy data:

In the above screenshot Cost is the metric I utilized.

Please let me know if you need any more clarification,
Lauren Griessel

Hi Lauren,

Thanks for the quick reply. 

I have figured out the monthly by including a date partition. 

Now - I am having some trouble with YTD reporting. The YTD dollars tie out however I am testing February and when I bring in the month in February - all line items are showing as February despite the dollars tying to both January and February combined. Does anyone know a way around this to show both January and February as months in February for YTD data?

@Gregory Nutt or @Lauren Griessel can you help? 

Hey Jaiveer,

I am not sure I 100% understand the concern here, but I do know that what you are asking for is possible within Apptio.  Here is how I would set up your data and the subsequent report.

Data set:

• First off, you will want to use your Date Partition so that every month within Apptio contains only the data for that month.  The goal here is to split data that looks like this to look like the following

This data will be backing your Modeled objects and you would presumably be tying your numeric data to a Metric.  In my above example, I detail Cost.  Since you're referring to a Month End report, I'd imagine you'd align with Cost as well.

Once you have this, you will want to take advantage of those Time options that @Lauren Griessel highlighted in her earlier response.  There are a few options that may be of use in displaying your data, but to your specific points, I would recommend the following:

• Prior Month Expense - Use the "Current Month" option into your Columns in your dialog.  Once there, you can right click and select a "Shift..." value to shift the Month view back by one, or -1.

• For the Year to Date value, there's a simple "Year to Date" option you can pull right into your Columns dialog.  No further config necessary on this one.

Lauren mentioned this in her response, but just to reiterate - these Time functions will only work if you are modeling against a Metric of some kind.

All of this was designed around Date Partition because this is the recommended path.  However, if there is a particular reason why you believe Date Partition is not acceptable within this data table, then there are ways around this in that direction as well.  If this is the case, let me know and I can walk you through how I would handle this while maintaining all monthly data within the data table.

-Greg

Hi Greg,

I actually found a workaround around getting the data to show what I would like it to show. 

I was applying YTD twice on the metric causing it to double count the YTD and not show properly. I worked with our CSM to get the report to show both YTD and MTD reporting with a date partition being included. 

Thanks!

Regards,

Jay