Forum Discussion

AleMacedo's avatar
AleMacedo
Helper I
5 years ago
Solved

Date error

I had to change an excel file to sharepoint list. Now my formulas in Power Bi show wrong results

 

I changed regional configuration in sharepoint list and power bi but didn't work.

 

My formula: 

"Projetos Finalizados no Prazo =
SWITCH (
TRUE (),
    FIRSTNONBLANK(cadProjetos[DataConclusãoPrevista],1)=BLANK(), UNICHAR(63),
    FIRSTNONBLANK(cadProjetos[DataTérminoReal],1)=BLANK(), UNICHAR(63),
    FIRSTNONBLANK(cadProjetos[DataTérminoReal],1)<=FIRSTNONBLANK(cadProjetos[DataConclusãoPrevista], 1 ), UNICHAR(9989),
UNICHAR (9949)
)"
 
DataConclusãoPrevista = Baseline Finish Date
DataTérminoReal = Finish Date
 
Término Est. = Baseline Finish Date
Términ0 = Finish Date
No prazo? = On time? 

 

 
 Don't know what to change, data in excel shows right simbols.
Any suggestion?
Thanks in advance.

 

 
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    AleMacedo , Try with these modifications

     


    Projetos Finalizados no Prazo =
    SWITCH (
    TRUE (),
    isblank(max(cadProjetos[DataConclusãoPrevista])), UNICHAR(63),
    isblank(max(cadProjetos[DataTérminoReal])), UNICHAR(63),
    max(cadProjetos[DataTérminoReal])<=max(cadProjetos[DataConclusãoPrevista]), UNICHAR(9989),
    UNICHAR (9949)
    )

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    AleMacedo , Try with these modifications

     


    Projetos Finalizados no Prazo =
    SWITCH (
    TRUE (),
    isblank(max(cadProjetos[DataConclusãoPrevista])), UNICHAR(63),
    isblank(max(cadProjetos[DataTérminoReal])), UNICHAR(63),
    max(cadProjetos[DataTérminoReal])<=max(cadProjetos[DataConclusãoPrevista]), UNICHAR(9989),
    UNICHAR (9949)
    )

  • AleMacedo's avatar
    AleMacedo
    Helper I
    5 years ago

    I create 2 new columns in sharepoint list to return "yes" or "no" to question: "On time?"

     

    what I have to change in formula to compare texts?

    If column= "yes" then show UNICHAR ( 9989 ), if column = "no", show UNICHAR ( 9949 ), if is blank() shows UNICHAR(63)?

     

    Projetos Iniciados no Prazo =
    SWITCH (
    TRUE (),
    FIRSTNONBLANK(cadProjetos[DataInícioPrevista],1)=BLANK(), UNICHAR(63),
    FIRSTNONBLANK(cadProjetos[DataInícioReal],1)=BLANK(), unichar(63),
    today () < FIRSTNONBLANK (cadProjetos[DataInícioPrevista], 1 ),"",
    FIRSTNONBLANK( cadProjetos[DataInícioReal], 1 )
    <= FIRSTNONBLANK ( cadProjetos[DataInícioPrevista], 1 ), UNICHAR ( 9989 ),
    UNICHAR ( 9949 )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi AleMacedo 

      Do you want to change your data source from excel to sharepoint list, and the measure you built will show wrong result after you change your data source?

      You build a measure by switch function. In No prazo?, all icons are unichar(9989). I need to know your data and data model. Could you share me a sample file by Onedrive for Business that will show error in measure? And it is better for you to show me the result you want by screenshot. This will make it easier for me to understand your measure logic.

       

      Best Regards,

      Rico Zhou

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

  • AleMacedo's avatar
    AleMacedo
    Helper I
    5 years ago

    I think that it was same error with unicode . I changed them (codes) and worked. Thank you all!