topic Re: Calculate difference between two month in Desktop

Calculate difference between two month

Anonymous — Wed, 02 Jan 2019 10:13:00 GMT

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

Re: Calculate difference between two month

AlB — Wed, 02 Jan 2019 10:57:01 GMT

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

Re: Calculate difference between two month

AlB — Wed, 02 Jan 2019 14:13:29 GMT

Anonymous

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

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