Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

PowerBI Dax

Hi, 

 

I currently have a central date table and would like to create a new central table that contains only the months 'Jan-Dec'. 
It could be simple, but I'm not sure how.  
 
Could you please advise?

 

DateTable = CALENDAR(VALUE("01/02/2021"),VALUE("01/12/2024"))
 
Thank you 
Need Help
Show and Tell
Tips and Tricks
  • PC2790's avatar
    PC2790
    4 years ago

    This can be done in two steps:

    1. Create a new table with the following code

    Table = GENERATESERIES(1,12)

    Outcome:

     

    2. Then create a new calculate column using the code:

    Month Name = 
VAR n = 'Table'[Value]
RETURN 
    IF(
        n > 0 && n < 13 ,
        -- THEN --
        FORMAT(DATE(2022,n,1) ,"mmmm"),
        -- ELSE --
        "Other"
        )

    Outcome:

    If you want to go for shortcut, just have the names of the months in excel, and using 'Enter Data' have a new table in your power bi model.

    As this is static, it is not going to change and make any difference.

5 Replies

  • rohit_singh's avatar
    rohit_singh
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi Anonymous ,

    Are you trying to create a table with a single column for months from Jan-Dec ? Something like 

    , or something else ? Please share your expected output.

    Kind regards,

    Rohit

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Yes, exactly that so I can connect other tables and use the months in a slicer. Thanks 

      • rohit_singh's avatar
        rohit_singh
        Icon for Solution Sage rankSolution Sage
        4 years ago

        Hi Anonymous ,

        Thanks for your response. Since you've mentioned you already have a central date table, I assume it has a month field available. You can leverage that to create your new table.

        If there is no month name on your date table, you can add a new column using your date column like 


        My date table is called dim_date


        I simply create a new table summarizing the months column and I get a new table with only months

        Months =

        SUMMARIZE(dim_date,
                             dim_date[Month Name])


        Please let me know if this works for you. 

        Kind regards,

        Rohit


        Please mark this answer as the solution if it resolves your issue.
        Appreciate your kudos! ğŸ˜Š




  • PC2790's avatar
    PC2790
    Icon for Community Champion rankCommunity Champion
    4 years ago

    This can be done in two steps:

    1. Create a new table with the following code

    Table = GENERATESERIES(1,12)

    Outcome:

     

    2. Then create a new calculate column using the code:

    Month Name = 
VAR n = 'Table'[Value]
RETURN 
    IF(
        n > 0 && n < 13 ,
        -- THEN --
        FORMAT(DATE(2022,n,1) ,"mmmm"),
        -- ELSE --
        "Other"
        )

    Outcome:

    If you want to go for shortcut, just have the names of the months in excel, and using 'Enter Data' have a new table in your power bi model.

    As this is static, it is not going to change and make any difference.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you 🙂