Forum Discussion
Helper IIIError when sorting by column (and table made with DAX language)
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'
Helper III
Solution Sage- DM_BI
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) +1Añ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
Anyone?
- LivioLanzo
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'