cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yuvarajg Member
Member

Calculate difference between two month

I have  following Columns with in a table

 

 StartMonth         EndMonth                   

 Jan-2017               Mar-2017               

 Nov-2017             Mar-2018              

 

want difference between Startmonth  and endmonth, How can i get following Column?

 

OutPut

3

5

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculate difference between two month

@yuvarajg

 

It looks like you can actually use something much simpler like:

 

NewColMonthDiff=
1+ DATEDIFF(DATEVALUE(Table1[StartMonth]), DATEVALUE(Table1[EndMonth]),MONTH)

 

 

2 REPLIES 2
Super User
Super User

Re: Calculate difference between two month

Hi @yuvarajg

It would be much easier if you had the dates in another format. How about a new column like this:

 

NewColMonthDiff=
VAR _StartMonthNum=SWITCH( LEFT(Table1[StartMonth],3),
                                 "Jan", 1,
                                 "Feb",2,
                                  ...
                                 "Dec",12)
VAR _EndMonthNum=SWITCH( LEFT(Table1[EndMonth],3),
                                 "Jan", 1,
                                 "Feb",2,
                                  ...
                                 "Dec",12)
VAR _StartYear=RIGHT(Table1[StartMonth],4)
VAR _EndYear  =RIGHT(Table1[EndMonth],4)
RETURN
1+ (_Endyear - _StartYear)*12 + _EndMonthNum - _StartMonthNum

 

where you would have to complete the two SWITCH statements with all the months and their numbers (where I've placed the '...')

 

 

 

    

Super User
Super User

Re: Calculate difference between two month

@yuvarajg

 

It looks like you can actually use something much simpler like:

 

NewColMonthDiff=
1+ DATEDIFF(DATEVALUE(Table1[StartMonth]), DATEVALUE(Table1[EndMonth]),MONTH)