4 Replies Latest reply on Sep 23, 2018 5:04 AM by Nancy Sidhu

# Dynamically change the latest months according to a fixed date, every month

Hey everyone!

I'm working on creating a calculation which enables me to look at past 3 months' data.

(DATETRUNC('month',[DATE])=DATETRUNC('month', DATEADD('month',-1,{[Max date]}))): For last month

(DATETRUNC('month',[DATE])=DATETRUNC('month', DATEADD('month',-2,{[Max date]}))): For second last month

(DATETRUNC('month',[DATE])=DATETRUNC('month', DATEADD('month',-3,{[Max date]}))): For third last month

This is working fine!

The issue is that I have to design a calculation based on following condition:

If the date of current month is upto 7th then the last 3 months excluding the current month should be displayed. If the date of current month is 7 or crosses 7, then the data of last 3 months including the current month should be displayed.

Example: Today is Sept 22,2018. So I want to see the data of Sept,Aug and July.

If today was Sept 2,2018 then I would want to see the data of Aug,July and June.

Any Suggestions?

TIA!

• ###### 1. Re: Dynamically change the latest months according to a fixed date, every month

Hi,

You can do it by using IF ELSE condition. For example,

IF

INT(DATENAME('day',Today()))<7

AND DATEDIFF('month',[DATE],Today())>0

AND DATEDIFF('month',[DATE],Today())<=3

THEN [Sales]

ELSEIF

INT(DATENAME('day',Today()))>=7

AND DATEDIFF('month',[DATE],Today())<3

THEN [Sales]

END

Regards,

Rahul

1 of 1 people found this helpful
• ###### 2. Re: Dynamically change the latest months according to a fixed date, every month

You may Try this also, I was able to Check for August Month in my Data and It works. I don't have August 7 in my data so I used 8, You can change it to 7.

This is the Formula

IF Day({MAX([Date])})>=8 THEN

ELSE

END

When I change my Dates to More Than Aug 8, I get This

and when I Change date to Below Aug 8 which is Aug 1 in my Dataset I get This

Thanks

Deepak

If it Helps, Pl Mark it Helpful and CORRECT to Close thread

1 of 1 people found this helpful
• ###### 3. Re: Dynamically change the latest months according to a fixed date, every month

Thanks Deepak!

• ###### 4. Re: Dynamically change the latest months according to a fixed date, every month

Thanks Rahul. Your method serves the purpose. But for this to work, I would have to make this calculation for every single field. Thanks for help anyway.