Convert Month Named Columns to Dates

[Asantos2020]

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

[Anonymous]

Make a new table as a key.

Month          ID

Jan                  1

Feb                 2

. . .                    . . .

Then merge that table with your current table.

[Asantos2020]

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]

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

[Asantos2020]

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

[Anonymous · Accepted solution]

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

[Asantos2020]

Well...just so this date gets updated as the files coming overtime, I've done it this way, then: FORMAT(DATE(YEAR(TODAY());4;1);"dd/MM/yyyy").

Thank you! Cheers!