Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

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

LotSN.png

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.

1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

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


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

View solution in original post

9 REPLIES 9
az38
Community Champion
Community Champion

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

Hi @az38 
I get this error
Error.pngwhat was wrong?

az38
Community Champion
Community Champion

@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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

@az38 
I had no empty data but one was wrong.
It no longer gives me an error, but does not show information.Annotation 2019-12-18 121541.png

az38
Community Champion
Community Champion

@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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

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?Screenshot (141).png

az38
Community Champion
Community Champion

@Anonymous 

share pbix-file, please, with data loaded into

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

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

 

Annotation 2019-12-18 133701.png

Anonymous
Not applicable

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors