5 Replies Latest reply on Feb 14, 2019 6:10 PM by Zhouyi Zhang

# How to calculate weeks from todays date to last 365 days.

Hi Experts,

I have a scenario on which i wanted to show number of records week over week from todays to last 52 weeks on a line graph.

e.g week1 = today()-7, week2= today()-14 and so on for last 52-53 week.

Can someone help me on this ?

I am attaching one of my workbook on which i have tried something.

I need all the 52 weeks in a graph if for few of the weeks number of records are 0 then those weeks also be present on the graph.

but currently after week 7  i am directly getting week no 46 which is not as per the requirement.

• ###### 1. Re: How to calculate weeks from todays date to last 365 days.

Hi, Sumit

To achieve this, you need a calendar table and join your data to this table as shown below

and the week with 0 record.

Workbook attached.

Hope this helps

ZZ

• ###### 2. Re: How to calculate weeks from todays date to last 365 days.

Hi ZZ,

Thanks for the help.

First i need to know how you have created that calendar ?

Second thing is I need dynamic Calendar which will calculate the week from today's date to last 365 days and weeks between them.

Currently the Calendar has only 2016 dates but in real scenario i wanted to calculate all these things from the current date.

• ###### 3. Re: How to calculate weeks from todays date to last 365 days.

Hi, Sumit

you can create calendar table in excel or your data source. in the sample workbook, I just add the calendar in your sample data excel

and in your real case, you can add a filter as below to filter the calendar dates only keep last 365 days

and

[calendar]<= today()

ZZ

• ###### 4. Re: How to calculate weeks from todays date to last 365 days.

Hi ZZ,

In my case i always wanted to calculate weeks from the today's date to last 365 days.

So every data date will be changed as current date and then i have to calculate weeks.

How we can achieve this using Calendar.

I need dynamic calendar on which only last 365 day dates should be present from the current date.

Is it possible ?

• ###### 5. Re: How to calculate weeks from todays date to last 365 days.

Hi, Sumit

The calendar must be static, but it could cover the date range from your earliest date to a feature date. and you the filter I suggest to filter the data with last 365 days only.

ZZ