Forum Discussion

DM_BI's avatar
DM_BI
Icon for Helper III rankHelper III
7 years ago
Solved

Error when sorting by column (and table made with DAX language)

Hello,   I created a DimDate (with a DAX formula calendar auto) and added natural months. Then I added a column "month_inter" because the start and end of my "month_inter" are very different from n...
Need Help
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    Hi DM_BI,

     

    you cannot sort mes inter by num because the value of num depends on mes inter. 

    Try to calculate your 'num' column the same way you calculate the 'mes inter' column but by assigning the month number instead of the month name. And then you can calculate the 'mes inter' based on the 'num' column. afterwards you should be able to sort 'mes inter' by 'num'

LivioLanzo's avatar
LivioLanzo
Icon for Solution Sage rankSolution Sage
7 years ago

Hi DM_BI

 

what is your dax that creates the date table?

  • DM_BI's avatar
    DM_BI
    Icon for Helper III rankHelper III
    7 years ago

    Hi LivioLanzo

     

    I used this one : DimDate = CALENDARAUTO()

    • LivioLanzo's avatar
      LivioLanzo
      Icon for Solution Sage rankSolution Sage
      7 years ago

      Hi DM_BI

       

      afterwards how are you adding the calculated columns?

      • DM_BI's avatar
        DM_BI
        Icon for Helper III rankHelper III
        7 years ago

        I added the following columns :

         

        Día = DAY(DimDate[Date].[Date])

         

        Num_Mes = MONTH(DimDate[Date].[Date])
         
        Mes = FORMAT(DimDate[Date];"MMM")
         
        Semana = 'DimDate'[Date] - WEEKDAY(DimDate[Date];2) +1
         
        Año = YEAR(DimDate[Date].[Date])
         
        Año academico = if(DimDate[Date]>=DATE(2018;11;05)&&DimDate[Date]<=DATE(2019;11;01);"2018/2019";IF(DimDate[Date]>=DATE(2017;11;06)&&DimDate[Date]<=DATE(2018;11;04);"2017/2018";IF(DimDate[Date]<=DATE(2017;11;05);"Antes")))
         
        Mes Inter = if(DimDate[Date]>=DATE(2017;11;06)&&DimDate[Date]<=DATE(2017;12;01);"Nov";if(DimDate[Date]>=DATE(2017;12;02)&&DimDate[Date]<=DATE(2018;01;07);"Dic";if(DimDate[Date]>=DATE(2018;01;08)&&DimDate[Date]<=DATE(2018;02;04);"Ene";if(DimDate[Date]>=DATE(2018;02;05)&&DimDate[Date]<=DATE(2018;03;04);"Feb";if(DimDate[Date]>=DATE(2018;03;05)&&DimDate[Date]<=DATE(2018;04;01);"Mar";if(DimDate[Date]>=DATE(2018;04;02)&&DimDate[Date]<=DATE(2018;05;06);"Abr";if(DimDate[Date]>=DATE(2018;05;07)&&DimDate[Date]<=DATE(2018;06;03);"May";if(DimDate[Date]>=DATE(2018;06;04)&&DimDate[Date]<=DATE(2018;07;08);"Jun";if(DimDate[Date]>=DATE(2018;07;09)&&DimDate[Date]<=DATE(2018;08;05);"Jul";if(DimDate[Date]>=DATE(2018;08;06)&&DimDate[Date]<=DATE(2018;09;02);"Ago";if(DimDate[Date]>=DATE(2018;09;03)&&DimDate[Date]<=DATE(2018;10;07);"Sep";if(DimDate[Date]>=DATE(2018;10;08)&&DimDate[Date]<=DATE(2018;11;04);"Oct";if(DimDate[Date]>=DATE(2018;11;05)&&DimDate[Date]<=DATE(2018;12;02);"Nov";if(DimDate[Date]>=DATE(2018;12;03)&&DimDate[Date]<=DATE(2018;12;30);"Dic";if(DimDate[Date]>=DATE(2018;12;31)&&DimDate[Date]<=DATE(2019;02;03);"Ene";if(DimDate[Date]>=DATE(2019;02;04)&&DimDate[Date]<=DATE(2019;03;03);"Feb";if(DimDate[Date]>=DATE(2019;03;04)&&DimDate[Date]<=DATE(2019;03;31);"Mar";if(DimDate[Date]>=DATE(2019;04;01)&&DimDate[Date]<=DATE(2019;05;05);"Abr";if(DimDate[Date]>=DATE(2019;05;06)&&DimDate[Date]<=DATE(2019;06;02);"May";if(DimDate[Date]>=DATE(2019;06;03)&&DimDate[Date]<=DATE(2019;06;30);"Jun";if(DimDate[Date]>=DATE(2019;07;01)&&DimDate[Date]<=DATE(2019;08;04);"Jul";if(DimDate[Date]>=DATE(2019;08;05)&&DimDate[Date]<=DATE(2019;09;01);"Ago";if(DimDate[Date]>=DATE(2019;09;02)&&DimDate[Date]<=DATE(2019;10;06);"Sep";if(DimDate[Date]>=DATE(2019;10;07)&&DimDate[Date]<=DATE(2019;11;03);"Oct";BLANK()))))))))))))))))))))))))
         
        And now my following steps are :
        - sorting Mes inter by the order I want (the year starts on November and ends in October because I work in education sector, Nov would be Month 1, December = 2, etc.)
        - create a column current month that put 1 if we are in the current Mes Inter and 0 if not
        - crete a column current quarter that put 1 if we are in the current Quarter that takes into account the start and end of my Mes Inter, and 0 if not.
         
         
         
        If you see a solution I would be so grateful.
         
        Thank you for your time,
         
        DM