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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Extract month and year and get the number of months in Power Query

Hello Experts!

I have Start Date and End Date as columns and I want to get number of months between the dates. But I will not be able to use  formula: 

 

 

 

Number.Round(Number.From(([ENDDATE] - [STARTDATE])/( 365.25 / 12 )) ,0 ) +1

 

 

 

Reason is, I want to take the whole month despite of the date. As an example. 

STARTDATEENDDATENumber of months
2/1/20222/1/202313
7/15/20223/15/20239
10/15/202111/30/20212
6/30/20229/30/20224
3/1/20234/30/202526

 

How can I arrive this in Power Query? Basically, I want to extract the month of each date and subtract and add one. But this is impossibe when years are different. Please help!

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

I think following formula would work as you want to take complete month

 

= Number.RoundUp(Duration.Days(Date.EndOfMonth([ENDDATE])-[STARTDATE])/(365.25/12))

 

OR

 

= Number.RoundUp(Duration.Days(Date.EndOfMonth([ENDDATE])-Date.StartOfMonth([STARTDATE]))/(365.25/12))

 

1.png

View solution in original post

3 REPLIES 3
v-stephen-msft
Community Support
Community Support

Hi @Anonymous ,


Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.


Best Regards,
Stephen Tao

ronrsnfld
Super User
Super User

Power Quer

 

=(Date.Year([ENDDATE]) -Date.Year([STARTDATE]))*12 + (Date.Month([ENDDATE])-Date.Month([STARTDATE]))+1

 

ronrsnfld_0-1653211993241.png

or, if you are using Power BI, you could use:

DAX

 

Number of Months = DATEDIFF([STARTDATE],[ENDDATE],MONTH)+1

 

 

Vijay_A_Verma
Super User
Super User

I think following formula would work as you want to take complete month

 

= Number.RoundUp(Duration.Days(Date.EndOfMonth([ENDDATE])-[STARTDATE])/(365.25/12))

 

OR

 

= Number.RoundUp(Duration.Days(Date.EndOfMonth([ENDDATE])-Date.StartOfMonth([STARTDATE]))/(365.25/12))

 

1.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors