Scenario:  My Project data is extracted from the source system as a YTD total, not a current month total.  But I want to show the spend for the current month.  The only way I can get the current month's spend is to take the spend from the current month and subtract the spend from the previous month (i.e. Sept YTD minus Aug YTD will give me September's spend).  How can I do this within a transform table which will then get appended to my Capital Project Master Data table?

I can do this in a metric, but I think from a reporting standpoint, it might not be optimal since, if my data source changes (which it has, BTW, and the new system gives me monthly spend values), then the metric will only calculate for the old data set, and I'd have bring in a new metric for the new data set, which is messy.

My thought was to use the LookupFromPath function, but I don't know if I can use it to get the prior date.  Here's what I'm trying:

=If(CurrentDate("MMM")="Jan",Actual,Actual-LookupFromPath(DOMAINNAME() & “:” & PROJECTNAME() & “/Data/” & CURRENTDATE()-1 & "/ProSight Extract Transform",Identifier,Identifier,Actual))

So, if you're in January, then just take the January number, otherwise take the current number and subtract the prior month's number.  This syntax isn't working, though.  I don't know if it doesn't like the CurrentDate()-1, or if it's something else (maybe I'm causing a circular reference).  If this can work, can someone correct my syntax?  Or if not, are there any ideas for how I can accomplish this?

Hi James,

What you are attempting is possible but will create a performance drain on the system.  I would approach this differently and instead create individual data sets for each month for the raw data.  Then create a transform of one of them and append the rest into it, including a new column for period.  Data loads would need to be performed at the beginning of the fiscal year.  Create one more transform of the transform with the appended data sets, add a row filter to it to split it into its respective periods and use a regular lookup to pull the prior month value in from the transform with all the appends.  The lookup will likely require a helper column to identify the prior periods.

There may be an easier way to do this but this is what I mapped out in my head.

Just to close the loop on this question, the formula is most likely failing because "CURRENTDATE()-1" is not valid.

You might be able to make this work in another way. First create a formula column to generate last month's date in Mmm:FYxxxx format. Then this column can be referenced in the lookupfrompath formula for the date. The below formulas should give you a string representing last month's fiscal period. You'll still need to add an If() condition for January. (NOTE: This would be even simpler if DurationOfMonth() could take a column name or numeric expression in the month parameter. However, it only accepts true numeric values at this time.)

Last Month Num=CurrentDate("M")*1-1 (*1 because CurrentDate returns a label by default)

Last Month Period=DateFormat(Last Month Num&"/1/"&CurrentDate("yyyy"),"ppp:ffff")