Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@Anonymous
It looks like you can actually use something much simpler like:
NewColMonthDiff= 1+ DATEDIFF(DATEVALUE(Table1[StartMonth]), DATEVALUE(Table1[EndMonth]),MONTH)
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 '...')
@Anonymous
It looks like you can actually use something much simpler like:
NewColMonthDiff= 1+ DATEDIFF(DATEVALUE(Table1[StartMonth]), DATEVALUE(Table1[EndMonth]),MONTH)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |