Forum Discussion

powerbiexpert22's avatar
powerbiexpert22
Impactful Individual
2 years ago
Solved

sort year month

how to sort year month column in line chart, i have yearmonth column in "YYYY-MMM" format example 2023-Jun

if i try to sort YearMonth calculated column by Month( numeric column) then it only sort YearMonth column by months

and if i try to sort YearMonth calculated column by Year then it only sort YearMonth column by year

how ro sort YearMonth by both Year and Month at same time, I need to show YearMonth in only "YYYY-MMM" format

pbix file : 

https://drive.google.com/file/d/1Isq7kS8ZS9DfzKrXW6I6e_b06pdJl6vd/view?usp=drive_link

 

 

 

 

Need Help
  • Chakravarthy's avatar
    Chakravarthy
    2 years ago

    Hi powerbiexpert22 
    Try to create a column using below code:

    Step 1:
    ===========
    YearMonth_NEW =
    VAR _MONTH = MONTH('Date'[Date])
    VAR _YEAR = YEAR('Date'[Date])
    VAR _Result = DATE(_YEAR,_MONTH,1)
    RETURN _Result
    Step 2:
    =======
    Change the Datatype as "Date"
    Change the Format as "mmmm,yyyy"
    From the top pane in Table View
     
    Step 3:
    =========
    Pull the above YearMonth_NEW column into line chart.
     
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi powerbiexpert22 - Please check the image provided above, it is coming as per year and month sorting only.

     

     

    Year-Month-Sort = YEAR('Date'[Date])*12+MONTH('Date'[Date])-1
     
    Hope it works
     
    still any discrepency, please share the expected output.

3 Replies

  • Chakravarthy's avatar
    Chakravarthy
    Resolver II
    2 years ago

    Hi powerbiexpert22 
    Try to create a column using below code:

    Step 1:
    ===========
    YearMonth_NEW =
    VAR _MONTH = MONTH('Date'[Date])
    VAR _YEAR = YEAR('Date'[Date])
    VAR _Result = DATE(_YEAR,_MONTH,1)
    RETURN _Result
    Step 2:
    =======
    Change the Datatype as "Date"
    Change the Format as "mmmm,yyyy"
    From the top pane in Table View
     
    Step 3:
    =========
    Pull the above YearMonth_NEW column into line chart.
     
  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi powerbiexpert22 - Please create a new calculated column as below:

    Year-Month-Sort = YEAR('Date'[Date])*12+MONTH('Date'[Date])-1
     
    sort by this column with YearMonth(x-axis)
     

     

    Hope it helps

     
  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi powerbiexpert22 - Please check the image provided above, it is coming as per year and month sorting only.

     

     

    Year-Month-Sort = YEAR('Date'[Date])*12+MONTH('Date'[Date])-1
     
    Hope it works
     
    still any discrepency, please share the expected output.