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 dates, for example: 01/01/2019, 02/01/2019, etc.

So I did start "unioning" them in a table, like this:

Table =UNION(
SELECTEDCOLUMNS(
'Sales';"Customer";'Sales'[Customer];"Date";DATE(YEAR(TODAY());MONTH(???);1);
SELECTEDCOLUMNS(
'Sales';"Customer";'Sales'[Customer];"Date";DATE(YEAR(TODAY());MONTH(???);1)...

However, I can't figure out how to turn the month names into the months I want (Jan = 1, Fev = 2, etc).

When I add 1 to Month(), I get different dates than 01/01/2019.

Any help is greatly appreciated.

 

Regards,

Antonio Santos

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

6 Replies

  • 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?