Forum Discussion

DM_BI's avatar
DM_BI
Helper 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'

DM_BI's avatar
DM_BI
Helper 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
DM_BI's avatar
DM_BI
Helper III
7 years ago

Anyone?

  • LivioLanzo's avatar
    LivioLanzo
    Solution Sage
    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'

    • DM_BI's avatar
      DM_BI
      Helper III
      7 years ago

      Hi LivioLanzo,

       

      Thank you very much it works !!!

       

      Best regards,

       

      DM_BI