If Month is January then report previous year

[helpme1977]

Hello All, first of all I would like to apologize if this already exists I have tried to search for it but had no success.

I am trying to create a filter which will allow an automated financial report to show the following logic

If current month is equal to January
Then show entire previous year
Else show previous month

Each portion of the following expression runs when used separately as a standalone filter, however when I add them together in an IF statement I receive the below error message. Any suggestions would be highly appreciated!

if ([Statistic Period (Month)] = '1')
then ([Statistic Period (Year)] = (year(getdate())-1))
else (((Year([Operational Reporting].[Jobfile].[Statistic Period]) = Year(getdate())) AND (MONTH([Operational Reporting].[Jobfile].[Statistic Period]) = MONTH(getdate())-1)))

[MFGF]

Hello All, first of all I would like to apologize if this already exists I have tried to search for it but had no success.

I am trying to create a filter which will allow an automated financial report to show the following logic

If current month is equal to January
Then show entire previous year
Else show previous month

Each portion of the following expression runs when used separately as a standalone filter, however when I add them together in an IF statement I receive the below error message. Any suggestions would be highly appreciated!

if ([Statistic Period (Month)] = '1')
then ([Statistic Period (Year)] = (year(getdate())-1))
else (((Year([Operational Reporting].[Jobfile].[Statistic Period]) = Year(getdate())) AND (MONTH([Operational Reporting].[Jobfile].[Statistic Period]) = MONTH(getdate())-1)))

Hi,

Using if-then-else constructs in a filter expression generally isn't a good idea. A filter needs to evaluate to a TRUE or a FALSE outcome, so using a Boolean-type expression is usually the way to go.

I would probably use the following expression:

(extract( month, current_date ) = 1 and [Statistic Period (Year)]  = extract( year, _add_years(current_date, -1)))
or
(extract( month, current_date ) <> 1 and [Statistic Period (Year)]  = extract( year, current_date) and [Statistic Period (Month)] = extract( month, _add_months( current_date, -1)))

Cheers!

MF.

[helpme1977]

Hi,

Using if-then-else constructs in a filter expression generally isn't a good idea. A filter needs to evaluate to a TRUE or a FALSE outcome, so using a Boolean-type expression is usually the way to go.

I would probably use the following expression:

(extract( month, current_date ) = 1 and [Statistic Period (Year)]  = extract( year, _add_years(current_date, -1)))
or
(extract( month, current_date ) <> 1 and [Statistic Period (Year)]  = extract( year, current_date) and [Statistic Period (Month)] = extract( month, _add_months( current_date, -1)))

Cheers!

MF.

Brilliant, thank you so much!!