How can I calculate the date based on a set of numbers?

[Anonymous]

Hi, I'm very new to Power BI, but I need to solve this little problem.

I have a set of number

s as data in a table in power bi

Example:
19001001

19001002
19001003
19002001
19002002
19002003

Where the first 2 digits (19) represent the year

The next 3 digits (001) represent the day of the year
and the last 3 (001) represent the part number on the day

I need to calculate the date in year and month, from the first 5 digits.
I would greatly appreciate your help, thank you.

[az38 · Accepted solution]

Hi @Anonymous 

try a new calculated column

Column = 
var yearstart=date(2000+left([LotnameSN];2);1;1)
var days = value(mid([LotnameSN];3;3))-1
return
yearstart+days

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[Anonymous]

Hi @az38 
I get this error
what was wrong?

[az38]

@Anonymous 

it looks like you have an empty values in LotsnameSN column or values which start with space " " symbols. please, check

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

@az38 
I had no empty data but one was wrong.
It no longer gives me an error, but does not show information.

[az38]

@Anonymous 

it works for me on your data sample. we need debug

try to create column 

yearstart=date(2000+left([LotnameSN];2);1;1)

 or share pbix-file on https://uploadfiles.io/ for example

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Export the data from the table to a CSV and open it and in the cvs if you show me data the same thing I did with the formula,

and gave me the same result.

startstart = date (2000 + left ([LotnameSN]; 2); 1; 1)

if the formula works, but it is my power bi that does not show the information.
and that column occupied it in the power query editor, does it do the same?

[az38]

@Anonymous 

share pbix-file, please, with data loaded into

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

@az38 
Really thank you very much friend !!!
It helped me a lot.
a big hello!

[Anonymous]

@az38 
I sent you a private message sharing the file, a greeting.