Forum Discussion

gdexter's avatar
gdexter
Frequent Visitor
8 years ago

Format Date Naming IMPOSSIBLE?

Dear Power BI Community,

 

Reaching out for help,

 

Below we have a list of dates. I would like to create a new column that simply contains the MONTH and YEAR information, in a "January 2016" format if possible. I have searched through several different threads, and tried copying the text formula from Excel, but alas all in vain.

WIDER CONTEXT FOR FINAL APPLICATION:

In case it is useful, I am including the wider context this question is coming from.

I want to create a dynamic text box that contains the name of the month and year based on the month selected in the filter. Once I create this new column, I will make a measure containing if and iscrossfiltered functions and use as my text box.

 

Thanks in advance for the help.

 

All the best,

Giovanni

Need Help
Tips and Tricks
Tutorial Requests

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi gdexter

     

    If you select the date column in FIelds panel, and then go to Modelling-> Format->Date Time-> There you have this format as a standard option. You can select MMMM,yyyy format which will display the date like January,2018 etc.

     

    Thanks

    Raj

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      If you need this in DAX, Please use this code.

       

      New_Date_Column= FORMAT(Table1[Date],"MMM-yyyy")

       

      Thanks

      Raj

      • gdexter's avatar
        gdexter
        Frequent Visitor
        8 years ago

        Hi Anonymous 

         

        Thank you for the suggestion.

         

        When I enter FORMAT using your formula, the formula errors if enter the my date table and column.

        ERROR MESSAGE:

        A single value for column 'Daily' in table 'Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

         

        Perhaps it will work using a Power Query instead.

        Do you know how I can do this in Power Query using M Language?

         

        Thanks,

        Gdexter

        Giovanni

         

         

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    8 years ago

    Hi gdexter,

     

    Remove the day level in your date hierarchy and check if it can meet your requirement.

     

    Regards,

    Jimmy Tao

    • gdexter's avatar
      gdexter
      Frequent Visitor
      8 years ago

      Hi all,

       

      v-yuta-msft I have been able to remove the day level in my hierarchy when using a table (Image below), but what I really need is a dropdown slicer.

       

       

      When viewing the date hierarchy for the slicer version, it only shows the year level (image below), is there a way to only show months and year in the filter?

       

       

      Best,

      Gdexter