Forum Discussion
Bi monthly Date table
Hi Guys
Has anyone ever create date table with Bi monthly view ?
For eg
Jan-Feb
March-April etc
and have these by fiscal view and calender view along with sort column ?
What will be the best way to do something like this
I have tried below and it works however i need wider community opinion
Bi monthly test =
Var _currentmnth = MONTH(dimDate[Date])
Var _nextmonth = month(DATEADD(dimDate[Date],1,MONTH))
Var _even = FORMAT(dateadd(dimDate[Date],-1,MONTH),"MMM")&"-"&format(dimDate[Date],"MMM")
Var _Odd = FORMAT(dimDate[Date],"MMM")&"-"&format(DATEADD(dimDate[Date],1,MONTH),"MMM")
Var _result = if(ISODD(_currentmnth) , _odd,_even)
return
_result
6 Replies
you solution is good. you can also try this
Column = if(ISODD(month('Table'[Date])),FORMAT('Table'[Date],"mmm")&"-"&FORMAT(EDATE('Table'[Date],1),"mmm"),FORMAT(EDATE('Table'[Date],-1),"mmm")&"-"&FORMAT('Table'[Date],"mmm"))sort = if(ISODD(MONTH('Table'[Date])),month('Table'[Date]),month(EDATE('Table'[Date],-1)))Thanks ryan_mayu
I think your sort will not work . once you have mutiple years
I am trying to get something like below . Please note my FY starts in Jul so calender month 7 = 1 and so on
SorT FY SorT Cal july - Aug 1 4 sep - oct 2 5 nov - dec 3 6 jan - feb 4 1 march - april 5 2 may - jun 6 3 if you want to display the data in different years, you can add the year in the column
sort = if(ISODD(MONTH('Table'[Date])),year('Table'[Date])&month('Table'[Date]),year('Table'[Date])&month(EDATE('Table'[Date],-1)))
