How to convert Year in string format to Year in date format

[lhern_ndez]

Hello,

I have a column of data with only years, unfortunately is in string format, I have tried using the Formatting MENU in Power BI without sucess.

I would like to have a DAX formula to convert the text 2018 into the same 2018 but in Date value as I need to use the year to use the function 'SamePeriodLastYear' and that requires me to have it in a date format.

I have tried different functions but everything shows me errors or says that it can not convert 2018 to date format; not sure what I am missing,

I will appreciate the help,

Thanks,

Luis

[ChrisMendoza · Accepted solution]

@lhern_ndez,

The integer 2018 by itself is not a Date value. A proper Date value would include Year, Month, Day as defined in https://msdn.microsoft.com/en-us/query-bi/dax/date-function-dax.

I think you have two options,

1. a proper date calendar to run your Time Intelligence against
2. a calculated column that looks something like the below to turn the value into a proper date.

=Date([your year column],1,1)

Option 1 is the better solution to run Time Intelligence as a complete list of possible date values is needed anyways.

[lhern_ndez]

Hello Chris,

Thanks for your help.

I do not have days in my data, only years. Should I still need your suggestion Number 1? If so, I am not sure I understand what you meant, could you please elaborate a bit more?

For point 2, I obtain the date in the following format 1/1/2018 which would be fine I think, how can I format it in the same formula to obtain only 2018? I tried a couple of formulas but shows me error,

Thanks!

Luis

[ChrisMendoza]

@lhern_ndez,

My intention was actually that for use of Time Intelligence Fuctions you are required to have a list of dates as seen in the documentation https://msdn.microsoft.com/en-us/query-bi/dax/sameperiodlastyear-function-dax. 

Without sample data I am very much making assumptions of your data however, i will make an attempt.

 I do not really believe converting your "year numbers" into dates  by what I stated is going to help much. While using the built-in function would be nice, if you had the necessary format, what if you took a different approach by writing your DAX in way?

What if you found the current year for your context and then wrote the measure that says give me the sum up until the currrent year minus 1? Meaning 2018 - 1 = 2017. So in essence you filter the sum to not include the current year transactions.

Something like:

Measure = 
VAR curYear = MAX(Table1[Year])
VAR result =
    CALCULATE(
        SUMX(
            Table1,
            Table1[Value]
        ),
        Table1[Year] = curYear - 1     
    )
RETURN result

Produces the following in my sample:

[lhern_ndez]

sorry for the delay, I was traveling

your first option and converted into 1/1/2018 and it works for what I need.

I will try option 2 but it should work,

Thanks for the help!

Luis