+ Start a Discussion
JimPDXJimPDX 

Funny Fiscal Formula

I could use the help of a report guru here. In our Force.com package we have a simple field that allows us to compare sales data from prior years with a "this year to date vs last year to date". Essentially we don't count any volume data for a month prior to this month. Seems easy enough.

 

 

IF(MONTH( DATEVALUE(gvn__Order_Date__c) ) <= (MONTH(TODAY()) + 12),  
gvn__Extended_Price__c , 0)

Now we have a customer whose Fiscal year starts on July 1st. This means that data for December should count in April but data in April shouldn't count in December. Does that make sense? I thought it would be as easy as adding 12 to the month or something but that fails pretty quickly.

 

netTrekker_ADnetTrekker_AD

How is your fiscal year set up in Salesforce? I am not certain, but I believe it makes a difference. Our company is a July 1- June 30 FY and I will have to double check but I believe these types of formulas work for us.

JimPDXJimPDX

We have it starting July 1 but this formula does not account for the company fiscal year at all, it is simply comparing 4 vs 10 (month of the year) and if one is larger than the other or equal, it counts the sales. 

netTrekker_ADnetTrekker_AD

Ah I was under the impression that if your FY is July-June, then July = 1 and June = 12, therefore December is 6 and April is 10.

 

I know that this is supported by SOQL. http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_select_date_functions.htm

 

EDIT: Note that if your company is set to Custom Fiscal Years this doesnt work. If its set to Standard Fiscal Year but starts in July, it should work.

JimPDXJimPDX

How, in a formula, can I get a July date to show as Month 1? That would definitely solve my problem! You need a function like FISCALMONTH(Date__c)

netTrekker_ADnetTrekker_AD

Sorry Jim... I guess what I said only works with SOQL and not with formulas... I will be interested to see if anyone can figure this out.

 

It would be a pain but could you use a CASE function to establish July = 1, ... June = 12?