Autonumber Function on Date Field is not returning required valu

[Ron1]

I am facing a very strange issue and I am unable to find a solution for the same.

Your help will be highly appreciated

I am using auto function on my date field. However during year transition it is not showing consistency.Please help me to fix this. 

Date	Autonumber
05.12.2019	31
12.12.2019	32
19.12.2019	33
26.12.2019	35
02.01.2020	34

You cal also refer the attached screenshot

[andoryuu · Accepted solution]

Autonumber will assign numbers based on the order that it reads data each time.  You have to read in your dates as date values and then order by that date value.  In the code below Table1 has your base data (in the wrong order on purpose) and assigns the "wrong" number.  Table2 orders by the date field (that is brought in as text) and gives the "wrong" result.  Table3 is what you want as the value is converted using date#() and then ordered by that field.  

Table1:
LOAD DateValue, FormattedDate, AutoNumber(DateValue,'Table1DateValue') as AutonumberOfDateValue, AutoNumber(FormattedDate,'Table1FormattedDate') as AutonumberOfFormattedDate;
load DateValue, Date#(DateValue,'dd.mm.yyyy') as FormattedDate inline [
DateValue
05.12.2019
12.12.2019
19.12.2019
02.01.2020
26.12.2019
];


NoConcatenate
Table2:
LOAD DateValue as OrderedDateValueByText, FormattedDate as OrderedFormattedDateByText, AutoNumber(DateValue,'Table2DateValue') as OrderedAutonumberOfDateValueByText, AutoNumber(FormattedDate,'Table2FormattedDate') as OrderedAutonumberOfFormattedDateByText
Resident Table1
ORDER BY DateValue;




NoConcatenate
Table3:
LOAD DateValue as OrderedDateValueByDate, FormattedDate as OrderedFormattedDateByDate, AutoNumber(DateValue,'Table3DateValue') as OrderedAutonumberOfDateValueByDate, AutoNumber(FormattedDate,'Table3FormattedDate') as OrderedAutonumberOfFormattedDateByDate
Resident Table1
ORDER BY FormattedDate;

[Ron1 · Accepted solution]

 Yes in my data based on load order it was not consistent.

Hence I applied order by clause on my date field, then applied auto number.

Which fixed the issue