Forum Discussion

kkoc3karan's avatar
kkoc3karan
Frequent Visitor
2 years ago

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 wil...
General Comment
Need Help
Show and Tell
Tips and Tricks
ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
2 years ago

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)))
 
 
  • kkoc3karan's avatar
    kkoc3karan
    Frequent Visitor
    2 years ago

    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
    • ryan_mayu's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      2 years ago

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

      • kkoc3karan's avatar
        kkoc3karan
        Frequent Visitor
        2 years ago

        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