Forum Discussion

Asantos2020's avatar
Asantos2020
Icon for Advocate II rankAdvocate II
7 years ago
Solved

Convert Month Named Columns to Dates

Hello,   I now have a table as the one below: Customer | Jan | Fev | Mar A                10     15     5 B                  5      7      8   ...and I need to turn those month names into date...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    I believe I am understanding correctly now.

     

    So the MOTNH( ) function actually works opposite. You give PBI a month, and it returns a number that coorespondes to that date.

     

     

    One way is the use = FORMAT(DATE(1, 4, 1), "MMM")

     

    The returned value for this is 'Apr'

     

     

    Let me know if this helps you out

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Make a new table as a key.

 

Month          ID

Jan                  1

Feb                 2

. . .                    . . .

 

Then merge that table with your current table.

 

 

  • Asantos2020's avatar
    Asantos2020
    Icon for Advocate II rankAdvocate II
    7 years ago

    Hello Anonymous ,

     

    I'm not sure I've made myself clear, but this table I'm creating is already supposed to be the result of another whose dates are in form of column header (month name) and they are supposed to be in date format, in the rows. I've done it with DATE(YEAR(TODAY());MONTH(TODAY())-1;1) for previous months sales and DATE(YEAR(TODAY());MONTH(TODAY());1) for current month's sales, but I cannot get the other months in date format, using DAX. Where would I pass in the months in the above DAX formula?

     

    Thanks a million!

     

    Regards,

    ASantos

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hmmm, could you share the file? Or a few screenshots?

      • Asantos2020's avatar
        Asantos2020
        Icon for Advocate II rankAdvocate II
        7 years ago

        Hi, Anonymous !

         

        Below, you can see the excel file from which I'm getting the data:

         

         

         

         

         

         

         

         

         

         

        You can see above that the column headers are month names and I need to consolidate them into a column, I can have it displayed on a timeline chart.

         

        Below, you can see the table I am creating:

         

        In the hilighted area, you can I tried MONTH(4), thinking it'd set the month to April, but the column Date underneath shows (01/01/2019). This is the biggest question!

         

        Can't thank you enough for looking into this.

         

        Regards,

        Antonio