Hi guys,

For some background: We wanted to be able to report on the Latest View figures for our Software/Hardware licenses (so all of the Actuals from previous months, then the forecast in our Latest View file) however, in our latest view file, previous months aren't updated with the actuals as they come in from our vendors. So, I created a Metric using formula that would take the Cost YTD we have from the invoices and the GL, and add it to the Forecast from the Latest View. Here's what I came up with: 

=Cost YTD+IF(CurrentDate("MM")="04",TimePeriod(Latest View,1m)+TimePeriod(Latest View,2m)+TimePeriod(Latest View,3m)+TimePeriod(Latest View,4m)+TimePeriod(Latest View,5m)+TimePeriod(Latest View,6m)+TimePeriod(Latest View,7m)+TimePeriod(Latest View,8m)+TimePeriod(Latest View,9m)+TimePeriod(Latest View,10m)+TimePeriod(Latest View,11m), IF(CurrentDate("MM")="05",TimePeriod(Latest View,1m)+TimePeriod(Latest View,2m)+TimePeriod(Latest View,3m)+TimePeriod(Latest View,4m)+TimePeriod(Latest View,5m)+TimePeriod(Latest View,6m)+TimePeriod(Latest View,7m)+TimePeriod(Latest View,8m)+TimePeriod(Latest View,9m)+TimePeriod(Latest View,10m), IF(CurrentDate("MM")="06",TimePeriod(Latest View,1m)+TimePeriod(Latest View,2m)+TimePeriod(Latest View,3m)+TimePeriod(Latest View,4m)+TimePeriod(Latest View,5m)+TimePeriod(Latest View,6m)+TimePeriod(Latest View,7m)+TimePeriod(Latest View,8m)+TimePeriod(Latest View,9m), IF(CurrentDate("MM")="07",TimePeriod(Latest View,1m)+TimePeriod(Latest View,2m)+TimePeriod(Latest View,3m)+TimePeriod(Latest View,4m)+TimePeriod(Latest View,5m)+TimePeriod(Latest View,6m)+TimePeriod(Latest View,7m)+TimePeriod(Latest View,8m), IF(CurrentDate("MM")="08",TimePeriod(Latest View,1m)+TimePeriod(Latest View,2m)+TimePeriod(Latest View,3m)+TimePeriod(Latest View,4m)+TimePeriod(Latest View,5m)+TimePeriod(Latest View,6m)+TimePeriod(Latest View,7m), IF(CurrentDate("MM")="09",TimePeriod(Latest View,1m)+TimePeriod(Latest View,2m)+TimePeriod(Latest View,3m)+TimePeriod(Latest View,4m)+TimePeriod(Latest View,5m)+TimePeriod(Latest View,6m), IF(CurrentDate("MM")="10",TimePeriod(Latest View,1m)+TimePeriod(Latest View,2m)+TimePeriod(Latest View,3m)+TimePeriod(Latest View,4m)+TimePeriod(Latest View,5m), IF(CurrentDate("MM")="11",TimePeriod(Latest View,1m)+TimePeriod(Latest View,2m)+TimePeriod(Latest View,3m)+TimePeriod(Latest View,4m), IF(CurrentDate("MM")="12",TimePeriod(Latest View,1m)+TimePeriod(Latest View,2m)+TimePeriod(Latest View,3m), IF(CurrentDate("MM")="01",TimePeriod(Latest View,1m)+TimePeriod(Latest View,2m), IF(CurrentDate("MM")="02",TimePeriod(Latest View,1m),0)))))))))))

(we start the Financial Year in April, hence the 04 at the start)

It works, as I've tested it by looking at the sum of the few months left in our financial year plus the Cost YTD we have already on reports, however I just don't like the look of the formula as it's so long. Does anyone know if there is a way to sum all of the values for a metric in the months going forwards up to the end of a financial year?

Cheers,

Oli

Whoa... that sir, is what we here in the "south" (on this side of the pond) call a "doozy".

It certainly is haha! I was looking at all the different formulas and functions trying to find a way to do it, and that was the only thing I could think of that would work, as everything else I could find function wise, was for previous months not future ones . Any idea what I can do to shorten it?

Oli,

One possible solution is to break up the formula into 11 individual metrics for the IF condition, and then combined those 11 metrics into a final metric + Cost YTD..

It's really the same thing but easier to maintain.

I solved similar issue with calculating Actuals YTD + Forecats till end of the year and I used this:

Actuals YTD + Anual(Forecast) - YTD(Forecast)

I think you can easily modilfy it for your case

Now that Oli is what us in the east end of London call a whoppa (technically that would have a swear word in front of it especially where I'm from)!!

Love the contrast between Oli's formula and Ivan's - hopefully it'll work (& save some calc time!)

It was a whopper indeed! I'd probably describe it to my mates as a beast of a formula.

I did manage to fix it in the end using a modified version of Ivan's formula, I just completely forgot about coming back here to update it!