convert numeric value to year (DATE field)

[Bbrown44]

I am trying to use one of the custom visuals from the Power BI Market place called 3AG Systems - Column Chart with Variance

It requires that the your category field be a date variable.  I have a date variable that Power BI only view as a numeric value.  I assume that is because is only the year (i.e., 2018).  How do I covert this numberic value to a date value.  The edit query field conversion does not change the value.  See example below:

Before

After

As you can see the date move to 1905. Please advise.

[jthomson · Accepted solution]

Well that's what 2016 as a numeric date value ought to convert to... assuming it doesn't really matter what date in a given year you need it to be, make a custom column saying "01/01/" and then merge that with your year column. It should then give you a format that you can convert to date type.

[Saiful20000]

Add number of days from 1900 to 2000

[JulianaMacedo]

Hi I'm facing a similar problem but couldn't find my solution with the suggested steps, I tried it but did not work.

I mean, it would be easier to add 2000+ to all numbers if I new all of them are from the year 2000 and ahead... but if I have any year bellow that then this does not work.

Is there a smart way to do this?

I have a column with 2-digit numbers that I need to transform into a new columsn with 4 digitis as the Year.

Column 1	Desired result
20	2020
21	2021
20	2020
19	2019
21	2021
19	2019

[v-lili6-msft · Accepted solution]

HI, @Bbrown44

After my test, the category field doesn't need a date variable.

for example:

I have three type of category: text, number, date

then I separate drag them into the category field of bar chart with variance.

They work well

Also If you want to convert numeric value to date.

you do it like this:

change the column type into Text first.

for example

then convert to date

Note:

When converting text to date and this window appears

Click Add new step

Otherwise it will not success.

Best Regards,
Lin

[matthewgierc]

By the way, you can find full documentation for this and our other Power BI visuals here:

https://www.3agsystems.com/blog/bar-chart-with-relative-variance

https://www.3agsystems.com/blog/column-chart-with-small-multiples

https://www.3agsystems.com/blog/bar-chart-with-absolute-variance

https://www.3agsystems.com/blog/column-chart-with-relative-variance

https://www.3agsystems.com/blog/column-chart-with-absolute-variance

[jthomson · Accepted solution]

Well that's what 2016 as a numeric date value ought to convert to... assuming it doesn't really matter what date in a given year you need it to be, make a custom column saying "01/01/" and then merge that with your year column. It should then give you a format that you can convert to date type.