Forum Discussion

ranambia's avatar
ranambia
Regular Visitor
9 years ago
Solved

DataFormat.Error: We couldn't convert to Date.

Hi all,

 

THis is Rahul, I am new to Power BI desktop and this is my first post. I have been trying out Power Bi Desktop for some days now as we are contemplating of using Power Bi on a large scale for our organisation. basically what I am trying to do first is by starting with replicatiing some SQL queries using M query language in Power Bi.

Say I want to replicate simple formulas like if Date1<=Date2 then 0 else 1

I am trying the following two formulaes,

#1

if (Date.From(DateTime.LocalNow())<=Date.From(Table2[DATE])) then 0  else 1

#2

else if(Date.From([DATE_1])=Date.From(Table2[DATE]) and [T_FLAG]=1) then 0 else 1

 

But on using this formulaes I am getting the same following error

DataFormat.Error: We couldn't convert to Date.
Details:
List

 

I tried using date time, changing the date format, data type but it is giving me the same error.

Kindly assist.

 

Thanks in advance,

 

Rahul.

General Comment
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    ranambia,

    Using M code, you are not able to reference different table in the formula, you can merge the two tables in a single table, then perform your calculation using M.

    Alternatively, change your DAX to the following code to get expected result.
    Column = IF(TODAY()<=Table2[C_DATE],0,IF(Table2[C_DATE]=RELATED(Table1[E_DATE]),0,1))

    Regards,

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    ranambia,

    I test the scenario as yours, everything works as expected. When using Date.From, please ensure that you enter the values of the types (described in this article) that can be converted to a data value.


    In addition, you can use DAX below to create expected column.

    Column = IF(TODAY()<=Table2[DATE],0,1)
    Column 2 = IF(Table2[DATE_1]=Table2[DATE]&&Table2[T_FLAG]=1,0,1)

    Regards,

    • ranambia's avatar
      ranambia
      Regular Visitor
      9 years ago

      Hi,

       

      Actually I am trying to perform this calculation using columns from multiple tables

      I tried the solution but it is still giving me the same error, is it because of the fact that the data is coming from two tables.

      I tried doing it with DAX calculation as well but it is giving me error when I am trying to reference a column from a different table even though I have established a relationship between them.

       

      Column = IF(TODAY()<=Table2[C_DATE],0,IF(Table1[E_DATE]=Table2[C_DATE].[Date],0,1))

       

      The underline denotes the error.

      Why is it giving me an error for columns from seperate table even after a relationship is established between both?

       

      Kindly help.

       

      Thanks in advance,

       

      Rahul.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        ranambia,

        Using M code, you are not able to reference different table in the formula, you can merge the two tables in a single table, then perform your calculation using M.

        Alternatively, change your DAX to the following code to get expected result.
        Column = IF(TODAY()<=Table2[C_DATE],0,IF(Table2[C_DATE]=RELATED(Table1[E_DATE]),0,1))

        Regards,