Hello,
I have to setup a dynamic filter condition for a monthly reporting.
Depending on the current date / month (today) the Report should include the previous month, the last two month or the last quarter
For example if I run the report in
January - the Report should include data from the last Quarter of the previous Year
February - the Report should include data only from January of the current Year
March - the Report should include data from January and February
April - the Report should include data from the previous Quarter (January to March)
Mai - only April data
June - April and Mai data
July - the previous Quarter (April to June)
and so on...
I try to solve this question with something like this:
if(MONTH = 01 , AddMonths(TODAY(), - 3),
if(MONTH = 02 , AddMonths(TODAY(), - 1),
if(MONTH = 03 , AddMonths(TODAY(), - 2),
if(MONTH = 04 , AddMonths(TODAY(), - 3),
if(MONTH = 05 , AddMonths(TODAY(), - 1),
if(MONTH = 06 , AddMonths(TODAY(), - 2),
if(MONTH = 07 , AddMonths(TODAY(), - 3),
if(MONTH = 08 , AddMonths(TODAY(), - 1),
if(MONTH = 09 , AddMonths(TODAY(), - 2),
if(MONTH = 10 , AddMonths(TODAY(), - 3),
if(MONTH = 11 , AddMonths(TODAY(), - 1),
if(MONTH = 12 , AddMonths(TODAY(), - 2)
)))))))))))) as CONDITION
But result of this formula is the calculated Day/Month and not the period of one or two Month or the last Quarter as whished.
Many thanks for your support!
Best regards,
Claus
