Forum Discussion

slorin's avatar
slorin
Super User
2 years ago

Hi

Date.From("01/01/0100", "en-EN") = #date(100, 1, 1) 

Date.From("12/31/0099", "en-EN") = #date(99, 12, 31)

Date.From("01/01/0001", "en-EN") = #date(1, 1, 1)

Date.From("01/01/99", "en-EN") =#date(1999,1,1)

Date.From("12/31/49", "en-EN") =#date(2049,12,31)

Date.From("01/01/50", "en-EN") =#date(1950,1,1) 

 

but 

Number.From(#date(100, 1, 1) ) = -657434

Number.From(#date(99, 12, 31) ) => error

 

Stéphane

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Stephane,

Thanks for the input.

We have a query, we are not getting the date ranges from 01/01/0001 to 12/31/0099 in the report view.

Kindly guides us haow to resolve this issue.

 

Thanks,

Priyanka 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous 

     

    It seems the date ranges from 01/01/0001 to 12/31/0099 are not supported in Power BI. Below is my test result. 

     

    From the official documentation, only years between 1900 and 9999 are designed to be supported. 

    Data types in Power BI Desktop - Power BI | Microsoft Learn

    DATE function (DAX) - DAX | Microsoft Learn

     

    Although the years between 0100 and 1899 appears supported at present according to the test, this cannot be always guaranteed as they are not in the designed allowed range.

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago

    If all you need to do is have them for a Power BI report, and you don't need to do any date calculations in Power BI, you could convert them to text strings.

     

    eg:

     

     

    #"Text Dates" = Table.TransformColumns(#"Previous Step",{"Date Column", each Date.ToText(_,"MM/dd/yyyy")})

     

     

    In the Advanced Editor of PQ, change "Previous Step" and "Date Column" to the appropriate step and column name as required. And the format to whatever it is you prefer.

     

    Above in Power BI