cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

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

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

1 ACCEPTED SOLUTION
Microsoft

@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
)
```

Microsoft

@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
)
```

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors