Calculate number of wees between 2 dates. If a date is blank use 1/1/2017

[ScORE]

Hello all,

I need help calculating the number of weeks between 2 columns.  The challenge is one of the columns may be blank at times.  If they are blank i'd like to the formula to use another date automatically.  For the purposes pf this example I have the following columns:

Start Date	End Date	# of weeks (need help calculating)
1/1/2017	5/5/2017
2/2/2017	5/5/2017
6/20/2017	5/5/2017
4/15/2017	5/5/2017
	5/5/2017
1/1/2017	5/5/2017
1/1/2017	5/5/2017
1/1/2017
	5/5/2017

- If the start date is missing, I'd like to use the beginning of the year (1/1/2017) as the start date to calculate the number of weeks.

- If the end date is missing, I'd like to use the current date (today) for the end date.  

- If both dates are present, I'd like to use those dates as is.  

- I will be summing the number of weeks to across different regions and groups to show the total number of weeks active.

I'm not sure if this is a measurement or if I need to add a column.  I'll be using this rolled up at different levels of  a hierarchy and need to calculations to adjust based on the what the user is filtering by.

Any help on this is greatly appreciated.  

ScORE

[Eric_Zhang · Accepted solution]

---

@ScORE wrote:

Hello all,

I need help calculating the number of weeks between 2 columns.  The challenge is one of the columns may be blank at times.  If they are blank i'd like to the formula to use another date automatically.  For the purposes pf this example I have the following columns:

Start Date	End Date	# of weeks (need help calculating)
1/1/2017	5/5/2017
2/2/2017	5/5/2017
6/20/2017	5/5/2017
4/15/2017	5/5/2017
	5/5/2017
1/1/2017	5/5/2017
1/1/2017	5/5/2017
1/1/2017
	5/5/2017

- If the start date is missing, I'd like to use the beginning of the year (1/1/2017) as the start date to calculate the number of weeks.

- If the end date is missing, I'd like to use the current date (today) for the end date.  

- If both dates are present, I'd like to use those dates as is.  

- I will be summing the number of weeks to across different regions and groups to show the total number of weeks active.

I'm not sure if this is a measurement or if I need to add a column.  I'll be using this rolled up at different levels of  a hierarchy and need to calculations to adjust based on the what the user is filtering by.

Any help on this is greatly appreciated.  

ScORE

---

@ScORE

Just create a calculated column as

weeks =
DIVIDE (
    IF ( ISBLANK ( yourTable[End Date] ), TODAY (), yourTable[End Date] )
        - IF (
            ISBLANK ( yourTable[Start Date] ),
            DATE ( 2017, 1, 1 ),
            yourTable[Start Date]
        ),
    7
)