Forum Discussion

grahamfletcher1's avatar
grahamfletcher1
Regular Visitor
8 years ago

Custom Date Format

I have a two parter around formating date fields for a matrix visual.

 

1) I have created a calculated column that uses the FORMAT function to create the date formatted as I need "MMM YYYY". This is working ok but it's not letting me sort the column by the actual date rather than the text version. Is there any way to keep this format but have it as a number not a text field?

 

2) I now want to flag some of the column headers in the matrix based on whether they are higher or lower than the date I have in another measure. This kind of works if I have the column formatted correctly then join an IF statement to return an extra string to add, it displays correctly with the added string but the columns that don't match the IF condition have an extra column with the added string and no data. I also tried doing the IF first and doing a different format using "MMM YYYY *", this doesn't work either. Does anyone have any ideas?

 

Below are my two attempts...

 

Month2 = IF([Control Date] > [MaxSubmittedMonth], FORMAT([Control Date], "MMM YYYY *", FORMAT([Control Date], "MMM YYYY"))

Month2 = FORMAT([Control Date], "MMM YYYY") & IF([Control Date] > [MaxSubmittedMonth], "*")

Here is the output of the second one which is closest.

 

 

The MaxSubmittedMonth measure outputs 30/06/2017 which is correct and you can see Apr 2017 has two columns, one without the * and correctly shows the data, but then has an extra column with the * and no data. The Aug 2017 column correctly displays one column with a star and correct data.

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    grahamfletcher1,

    Firstly, it is not possible to keep the calculated column "MMM YYYY" format and have it as a number. You can create another calculated column using formula: Month= Month(Table[Date]), then sort the first calculated column by this Month column. If you still get error, please post error message.

    Secondly, you can create Month2 column using DAX below.

    Month2 = IF('Table'[Control Date]>[ MaxSubmittedMonth],FORMAT('Table'[Control Date],"MMM YYYY"), FORMAT('Table'[Control Date],"MMM YYYY") & "*")



    Regards,
    Lydia

    • grahamfletcher1's avatar
      grahamfletcher1
      Regular Visitor
      8 years ago

      Hi Lydia,

       

      Thanks for your reply. Is there any way to get dates to format with the shorthand for the month name and keep it as a date? The options for date format don't have it in.

       

      I've put your formula in and it gives me the same output as the second expression I gave with the duplicated column with no data for months that don't meet the IF condition. Any ideas?

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        grahamfletcher1,

        You can change format of date column as shown in the following screenshot.


        The DAX works well in my PBIX file. Would you mind sharing me your PBIX file so that I can test? You can send me PBIX file via private message,

        Regards,
        Lydia