Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kkoc3karan
Frequent Visitor

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 6
ryan_mayu
Super User
Super User

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)))
 
11.PNG
 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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 FYSorT Cal
july - Aug 14
sep - oct25
nov - dec36
jan - feb 41
march - april 52
may - jun63

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




This is what i did to get what i wanted 

 

Bi monthly Sort FY =
Var _result = if(dimDate[Fiscal MonthNumber]+1 =1 , 1 ,QUOTIENT(dimDate[Fiscal MonthNumber]+1,2))
return
_result

 

Bi monthly Sort CAL =
Var _result = if(dimDate[Calendar MonthNumber]+1 =1 , 1 ,QUOTIENT(dimDate[Calendar MonthNumber]+1,2))
return
_result

 

usually we should have different solutions. if this works, then you can use it.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Do u know if there is a norm / best practise for Bi monthly time scale ? 

 

I dont want to use things which are not best practise / norm 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.