Converting month and Year in Separate Columns to One

[dineshj23]

Hi, 

I have the data in in this format. I have several columns for dates; Invoice1 date, Invoice2 date, for a total of 12 dates. Each month and date for these 12 dates is in different columns as in the chart below. I wanted to create a date for each of the dates to be simple like 1/1/2022. 

I tried doing this with =1*("1/"&'date'[Month]&"/"&'date'[Year]) but got the below errors. 

Then I tried "=format(Date" formula but got the below error. 

Is there a way to do this? 

Also, I have a seperate calendar built for the next two years. I want to place a chart view so I can see I have 3 invoices due in March 2023. This would be building a relationship with the calendar and the dataset above. How can I structure this so it works that I can see the 3 invoices thare due in March or 6 invoices in April. Sort of like a timeline. 

Thank you for your help. 

Thank you. 

[dineshj23]

Hi @Anonymous 

I used the formula you sent above and got this error. 

[Anonymous]

Hi @dineshj23,

My formula is creating a new column on your table to invoke the year and month fields and use DATEVALUE function of convert the concatenate values to date format.
Did these fields include blank values? If that is the case, you can add condition to check them at first and setting a default value as if statement false branch result.(in my formula, I used the current system date as default value)

Transform Date =
IF (
    Table[Month] <> BLANK ()
        && Table[Year] <> BLANK (),
    DATEVALUE ( Table[Month] & "/1/" & Table[Year] ),
    TODAY ()
)

Regards,

Xiaoxin Sheng

[Anonymous · Accepted solution]

HI @dineshj23,

You can use the following calculated column formula to concatenate the year month and convert the result to date:

Transform Date =
DATEVALUE ( [Month] & "/1/" & [Year] )

Regards,

Xiaoxin Sheng