This formula will give the financial year a date occurred in.
This assumes that the date is in A1.
This can be altered for acadamic year by replacing the 4 with a 9 (academic months start in September, not April)
You can add comments In expressions by using the following
;#/*your comments here*/#;
As with any programming the better documented your report and code is, the easier it is to maintain.
This little snippet is used in a filter and automatically looks at the previous calendar month.
cast([Sales Team].[Client Contacts].[Start Date],date)
If the date is left as a timestamp then the last of the month is 2013-04-30 00:00 which means that a client contact that occurs at 9am would not be picked up. Casting it as a date ensures that it gets picked up as expected.
Another option is to add 1 day onto the _last_of_month, but that’s a hack.
The _add_months function can accept a negative number, which turns _add_months into _substract_months.
This snippet takes an ID number and displays it in the format of 3-3-4 (e.g. 123 456 7890)
substring(cast([Customers].[Account Detail].[Account Number],varchar(10)),1,3)+' '+
substring(cast([Customers].[Account Detail].[Account Number],varchar(10)),4,3)+' '+
substring(cast([Customers].[Account Detail].[Account Number],varchar(10)),7,4)+' '
Its a simple substring operation, withthe account number being cast as a 10 character string