11 Replies Latest reply on Oct 8, 2018 1:32 PM by Satish Pandey

# Dynamic calculation to retrieve data for current + next 3 months

I have a dashboard using excel as a data source.

There is a requirement where I need to show data only for the current month and the next 3 months.

I need to know the way to dynamically filter the data for current month(buildout month) + next 3 months.

I have attached a sample twbx file for the reference.

Pls advise.

Simon Runc

• ###### 2. Re: Dynamic calculation to retrieve data for current + next 3 months

Thanks for sharing the links however nothing worked for my case.

I have attached my test sheet with the original question. Can anyone look at it ?

Simon Runc   please advise.

I want to filter my attached report dynamically for the current month (Buildout Deadline) + next 3 months.

• ###### 3. Re: Dynamic calculation to retrieve data for current + next 3 months

Satish,

Create calc column as below:

[Buildout Deadline] >= TODAY() and [Buildout Deadline] <= DATEADD('month',3,[Buildout Deadline])

and put this on filter shelf.

• ###### 4. Re: Dynamic calculation to retrieve data for current + next 3 months

Satish,

Please modify your formula a bit to consider full months. First create a custom date field from your existing date choose month year option then use below formula on this new field and put this on filter shelf.

([Buildout Deadline (copy) (Month / Year)]) >= int(str(year(TODAY()))+str(MONTH(TODAY())))

AND

[Buildout Deadline (copy) (Month / Year)] <= int(str(year(DATEADD('month',3,[Buildout Deadline])))+str(MONTH(DATEADD('month',3,[Buildout Deadline]))))

• ###### 5. Re: Dynamic calculation to retrieve data for current + next 3 months

Looks like you have some good solutions there...let me know if they don't do the trick.

• ###### 6. Re: Dynamic calculation to retrieve data for current + next 3 months

Hi Ankit,

Thanks for the reply.

The calculation works for some part but not fully. It doesn't pick the data for the month of July.

Any thoughts?

[Buildout Deadline] >= TODAY() and [Buildout Deadline] <= DATEADD('month',3,[Buildout Deadline])

• ###### 7. Re: Dynamic calculation to retrieve data for current + next 3 months

Ankit, I followed these steps and this formula picking the July data but not the Oct month data. I tried changing to 4 as well but no luck.

([Buildout Deadline (copy) (Month / Year)]) >= int(str(year(TODAY()))+str(MONTH(TODAY())))

AND

[Buildout Deadline (copy) (Month / Year)] <= int(str(year(DATEADD('month',3,[Buildout Deadline])))+str(MONTH(DATEADD('month',3,[Buildout Deadline]))))

• ###### 8. Re: Dynamic calculation to retrieve data for current + next 3 months

Satish,

My bad , use the below formula. Also now you need not create custom date.

int(str(year([Buildout Deadline]))+ str(MONTH([Buildout Deadline]))) >= int(str(year(TODAY()))+ str(MONTH(TODAY())))

AND

int(str(year([Buildout Deadline]))+ str(MONTH([Buildout Deadline])))  <= int(str(year(DATEADD('month',4,TODAY())))+str(MONTH(DATEADD('month',4,TODAY()))))

• ###### 9. Re: Dynamic calculation to retrieve data for current + next 3 months

Also for 3 months you need 3 only not 4.

int(str(year([Buildout Deadline]))+ str(MONTH([Buildout Deadline]))) >= int(str(year(TODAY()))+ str(MONTH(TODAY())))

AND

int(str(year([Buildout Deadline]))+ str(MONTH([Buildout Deadline])))  <= int(str(year(DATEADD('month',3,TODAY())))+str(MONTH(DATEADD('month',3,TODAY()))))

1 of 1 people found this helpful
• ###### 10. Re: Dynamic calculation to retrieve data for current + next 3 months

Thanks Ankit. It worked as expected.

• ###### 11. Re: Dynamic calculation to retrieve data for current + next 3 months

Hi Ankit,

Everything works well until last month, however, it's not working for Oct'18 onwards.

I can see there is a record for Oct,18 but below logic is not picking somehow. Can you please advise?

int(str(year([Buildout Deadline]))+ str(MONTH([Buildout Deadline]))) >= int(str(year(TODAY()))+ str(MONTH(TODAY())))

AND

int(str(year([Buildout Deadline]))+ str(MONTH([Buildout Deadline])))  <= int(str(year(DATEADD('month',3,TODAY())))+str(MONTH(DATEADD('month',3,TODAY()))))