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.

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 ?

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:

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

• ###### 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,

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

Any thoughts?

• ###### 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

• ###### 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.

AND

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

Also for 3 months you need 3 only not 4.

AND

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?