1 2 Previous Next 18 Replies Latest reply on Mar 24, 2018 4:07 PM by pavani.p.1

# Fiscal Year to Date sales

hello All,

I've been using the following calculated field to sum my sales as of year to date. The problem is, I need to adjust to be fiscal year to date. Any suggestions as how this could be done?

IF DATEDIFF('year',[Month-Year],[Reference Date])=0

AND

MONTH([Month-Year])<=MONTH([Reference Date])

THEN

[Order Value (USD)]

END

• ###### 1. Re: Fiscal Year to Date sales

Let's assume your fiscal year starts in April. You are going to translate fiscal year to calendar year, so that your YTD calculation will still work.

1) Create a calculated field called "Adjusted Month-Year" - April becomes January, May becomes February, etc:

2) Create a calculated field called "Adjusted Reference Date" that does the same for your reference date parameter:

DATEADD('month', 9, [Reference Date])

3) Update your measure to use the adjusted dates instead of your current dates:

AND

THEN

[Order Value (USD)]

END

If your fiscal year starts in July, then you'll to need change the first two calculations to offset by 6 months instead of 9. If it starts in October, then offset by 3 months.

Hope this helps,

-Steve

2 of 2 people found this helpful
• ###### 2. Re: Fiscal Year to Date sales

Thank you- worked like a charm!

• ###### 3. Re: Fiscal Year to Date sales

Hi Steve,

Our current FiscalYear Start's from July .

I am looking to Calculate YTD , QTD  Sales can i use the above ??

Regards,

Satish.p

• ###### 4. Re: Fiscal Year to Date sales

[Month-Year]what does it signify??

• ###### 5. Re: Fiscal Year to Date sales

DATEADD('month', 9, [Month-Year]) -------------[Month-Year]??????

DATEADD('month', 9, [Reference Date])   [Reference Date]-----???

• ###### 6. Re: Fiscal Year to Date sales

Hi,

We can define the First month of Fiscal Year.

Best Regards

Kumar

• ###### 7. Re: Fiscal Year to Date sales

i am not getting your point .

please provide me the  calculation for ytd Calculation according to fiscal year

• ###### 8. Re: Fiscal Year to Date sales

Satish -

You should be able to use the above technique but offset by 6 months instead of 9 months because your fiscal year starts in July. You should study the workbook from the correct answer submission to understand how the offset is working, because it is pretty tricky.

-Steve

• ###### 9. Re: Fiscal Year to Date sales

Let's assume your fiscal year starts in April. You are going to translate fiscal year to calendar year, so that your YTD calculation will still work.

1) Create a calculated field called "Adjusted Month-Year" - April becomes January, May becomes February, etc:

2) Create a calculated field called "Adjusted Reference Date" that does the same for your reference date parameter:

DATEADD('month', 9, [Reference Date])

3) Update your measure to use the adjusted dates instead of your current dates:

AND

THEN

[Order Value (USD)]

END

Problem :   [Month-Year] in above calcluation i am not getting this filed it will be my posting date or some other calculated field.

[Reference Date] not getting

• ###### 10. Re: Fiscal Year to Date sales

Shankar - you might ask Drew to attach his workbook so that you can see how he did it.

[Month-Year] is the name of his date dimension in his data source. You'll have to substitute the name of your date dimension for [Month-Year].

[Reference Date] is the name of his date parameter in his data source. You will also have to create a date parameter in your data source, and update the calculated field with the name of your date parameter.

Hope this helps,

-Steve

• ###### 11. Re: Fiscal Year to Date sales

Let's assume your fiscal year starts in April. You are going to translate fiscal year to calendar year, so that your YTD calculation will still work.

1) Create a calculated field called "Adjusted Month-Year" - April becomes January, May becomes February, etc:

2) Create a calculated field called "Adjusted Reference Date" that does the same for your reference date parameter:

DATEADD('month', 9, [Reference Date])

3) Update your measure to use the adjusted dates instead of your current dates:

AND

THEN

[Order Value (USD)]

END

Problem :   [Month-Year] in above calcluation i am not getting this filed it will be my posting date or some other calculated field.

[Reference Date] not getting

• ###### 12. Re: Fiscal Year to Date sales

1) Create a calculated field called "Adjusted Month-Year" - April becomes January, May becomes February, etc:

DATEADD('month', 9, [Month-Year])-----------------????from where we will get [Month-Year]

2) Create a calculated field called "Adjusted Reference Date" that does the same for your reference date parameter:

DATEADD('month', 9, [Reference Date])----------------????from where we will get [Reference Date]

3) Update your measure to use the adjusted dates instead of your current dates:

AND

THEN

[Order Value (USD)]

END