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
SophRow
Resolver II
Resolver II

Calculate difference between dates, use TODAY for blanks

Hi Experts,

 

I want to calculate Tenure in months and have a Tenure start date column and a Termination date column. 

 

If the termination date column is blank, I want to calculate the number of months between tenure start date and today. 

 

The output would be like: 

 

SophRow_0-1683699523108.png

Any ideas for a nice DAX formula?

 

Best,

Soph

1 ACCEPTED SOLUTION
Tahreem24
Super User
Super User

@SophRow  TRy this DAX Column:

 Tenure Month = 

VAR NewTermDate  = IF(TerminationDate=BLANK(), TODAY(),TerminationDate)

RETURN DATEDIFF(Tenure Start Date, NewTermDate, MONTH)

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

View solution in original post

1 REPLY 1
Tahreem24
Super User
Super User

@SophRow  TRy this DAX Column:

 Tenure Month = 

VAR NewTermDate  = IF(TerminationDate=BLANK(), TODAY(),TerminationDate)

RETURN DATEDIFF(Tenure Start Date, NewTermDate, MONTH)

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

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.

Top Solution Authors