Expression for the last day of the month based on Accounting Period

Database
Enthusiast

Expression for the last day of the month based on Accounting Period

=Year(DateAdd("m", -5, DateValue(Parameters!PD2.Value & "/01/" & Parameters!FY.Value))) & "-" & iif(Month(DateAdd("m", -5, DateValue(Parameters!PD2.Value & "/01/" & Parameters!FY.Value)))>9,Month(DateAdd("m", -5, DateValue(Parameters!PD2.Value & "/01/" & Parameters!FY.Value))), "0" & Month(DateAdd("m", -5, DateValue(Parameters!PD2.Value & "/01/" & Parameters!FY.Value)))) & "-" & Day(DateAdd("d", -1,DateAdd("m", -4, DateValue(Parameters!PD2.Value & "/01/" & Parameters!FY.Value)))) & " 23:59:59"

I'm trying to modify an existing report that has 1 prompt for Accounting Period. The new report will have 2 prompts: (1) Accounting Period From and (2) Accounting Period To. The user selects any value from 1 to 12 for each of these prompts.

Accounting Period 1 = August (month 8), Accounting Period 2 = September (month 9), etc.

The report will also have 2 internal prompts: (1) Date From and (2) Date to. The internal prompts are updated with a date based on an expression that converts the accounting period to a date timestamp.

I'm having trouble with the 2nd prompt (Accounting Period To). Whatever value the user selects should be converted to the last day of the corresponding month. For example, Accounting Period 4 is November, Accounting Period 5 is December, etc. If the user selects period 5 as the Accounting Period To, the date I'm looking for is 12/31/2015 23:59:59.

Following is the expression that I tried to modify to give me the last day of the month. PD2 is the parameter where the user selects the Accounting Period To. If I select Period 5, I'm expecting this expression to return 12/31/2015 23:59:59:

The expression was developed by someone else and I'm having a hard time understanding what it is trying to do.  Thanks for your help.

3 REPLIES
Senior Apprentice

Re: Expression for the last day of the month based on Accounting Period

*Existing report*, what reporting tool?

Do you need to do the calculation within the tool or using Teradata SQL?

Is this based on the actual date? E.g. if the user selects "period 2" today, what should be returned, Spetember 2015 or September 2016? In the calculation there's also a "FY" (=financial year?) parameter.

Enthusiast

Re: Expression for the last day of the month based on Accounting Period

The report is in Report Builder.  The report will calculate these dates and the dates will be used in Teradata SQL to return the results.  The fiscal year changes August 1st and the accounting periods are based on the current fiscal year.  If the user picks periods 1 - 5 then the year is 2015.  If they pick periods 6 - 12, then the year is 2016.  Thanks for looking at this

Enthusiast

Re: Expression for the last day of the month based on Accounting Period

Sorry...............I looked at the report again and there is a year prompt.  The user will select either 2014, 2015, 2016, etc.