Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ScORE
Helper I
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/20175/5/2017 
2/2/20175/5/2017 
6/20/20175/5/2017 
4/15/20175/5/2017 
 5/5/2017 
1/1/2017  5/5/2017 
   
1/1/20175/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
Eric_Zhang
Microsoft Employee
Microsoft Employee


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

Capture.PNG

View solution in original post

1 REPLY 1
Eric_Zhang
Microsoft Employee
Microsoft Employee


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

Capture.PNG

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.