Forum Discussion
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
Anonymous
It looks like you can actually use something much simpler like:
NewColMonthDiff= 1+ DATEDIFF(DATEVALUE(Table1[StartMonth]), DATEVALUE(Table1[EndMonth]),MONTH)
2 Replies
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 - _StartMonthNumwhere you would have to complete the two SWITCH statements with all the months and their numbers (where I've placed the '...')
Anonymous
It looks like you can actually use something much simpler like:
NewColMonthDiff= 1+ DATEDIFF(DATEVALUE(Table1[StartMonth]), DATEVALUE(Table1[EndMonth]),MONTH)