Calculate difference between two month

[Anonymous]

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

[AlB]

Hi @Anonymous

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 '...')

[AlB · Accepted solution]

@Anonymous

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

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