Forum Discussion

Karolina411's avatar
Karolina411
Helper V
4 years ago

Date mismatches

Hello

So...my date column in my date dimension is not working all of a sudden----I tried to ensure both are dates only but that is not working - when I use MONTHDATE (as below) it cannot get into date order.  Surely this is silly .....any suggestions ??  I just need a simple solution on how to display month/year by the month name/yr and suddently my date table seems to be a string value

 

 

Date Issues DateTime

12 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi Karolina411 

     

    Add a calculated column based on your date to show the YYYYMM like 202101=Jan-21

     

    then create another column to show the MMMM YY like January 2021, and sort it by the previous ↑ calculated column.

     

    Then you can use it in your report.

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    Hi:

    CALC COL for Date Table. This will allow the sorting you want but also is very helpful when you are trying to zone in on particular months, or groups of months. Lot's of applications!

     

    Running Month Index =

    VAR minyear = YEAR(MIN(Dates[Date]))

    VAR thisyear = YEAR(Dates[Date])

    Return

    (thisyear - minyear) * 12 + MONTH(Dates[Date])

    • Karolina411's avatar
      Karolina411
      Helper V
      4 years ago

      I can hardly understand how he wrote those complex instructions....how can there be  a program that does not sort dates in chronological order?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Right Click "DIMDATE", Add a new column, and type in the following code:

         

        yearMonth = RIGHT(YEAR('DIMDATE'[DateValue]),2) &
        SWITCH(MONTH('DIMDATE'[DateValue]),
        1,"01",
        2,"02",
        3,"03",
        4,"04",
        5,"05",
        6,"06",
        7,"07",
        8,"08",
        9,"09",
        10,"10",
        11,"11",
        12,"12"
        )

         

        Then Left Click the "FormattedMonthYear" under "DIMDATE" and sort the column by "yearMonth"