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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
KossithAz
Frequent Visitor

Calulate months open but considers dates with end dates and those with none

Hi folks,

Looking at a dataset where I'm trying to work out the duration of months open from the start date.

Example:

Start dateEnd dateMonths Open
05 December 202310 December 2023 
06 May 202201 April 2023 
07 November 2022  

 

I was previously using 

Months open = DATEDIFF('inYear'[Start date],TODAY(),MONTH)

But sadly this keeps adding months onto those that have ended.

So ideally, I need the months since the start date, but stops for those with an end date and continues on for those without an end date.
1 ACCEPTED SOLUTION
DOLEARY85
Super User
Super User

Hi,

 

try something like this:

 

Months Open = IF( ISBLANK('inYear'[End date]), DATEDIFF('inYear'[Start date], TODAY(), MONTH), DATEDIFF('inYear'[Start date], 'inYear'[End date], MONTH) )

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

View solution in original post

2 REPLIES 2
DOLEARY85
Super User
Super User

Hi,

 

try something like this:

 

Months Open = IF( ISBLANK('inYear'[End date]), DATEDIFF('inYear'[Start date], TODAY(), MONTH), DATEDIFF('inYear'[Start date], 'inYear'[End date], MONTH) )

 

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Thank you, that has worked wonderful!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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