function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion

Funny Fiscal Formula

I could use the help of a report guru here. In our 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.



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.


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. 


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.


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.


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)


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?