Forum Discussion

Aleksis's avatar
Aleksis
Regular Visitor
3 years ago

DATEDIFF presenting wrong results

Hello,

 

I'm quite new to PowerBI and have been trying to solve a problem related to date differences. I have looked into other posts but none of the solutions presented worked for me.

 

I have a bunch of projects with due dates. For some, due dates have already passed and for others, they are at some point in the future. Certain projects have the same due date and  I would like to add a column in my table (in the report) which shows if the project is overdue and by how many days.

 

Unfortunately, some of the results from the formula I use are incorrect. Furthermore, Due Date seems to be of text/string type, but I'm not sure. So as a result I have the following output:

The date format is (supposed to be) MM/DD/YYYY. As you can see, certain calculations are correct, certain calculations switch the month with the day, and certain ones are simply wrong.

 

I used the following formulas:

Number of days overdue = SUMX(PROJECTS, datediff([Due_Date],TODAY(), DAY))
Overdue = IF(SUMX(PROJECTS, datediff([Due Date],TODAY(), DAY))>0,"Overdue","-")

 

I have a live connection to a dataset so I can't create another table in the dataset. I have tried formating, changing functions, etc. based on other posts but nothing worked.

 

Please let me know if this is possible to do. Thanks in advance for your help.

Need Help

6 Replies

  • hannibalmads's avatar
    hannibalmads
    Advocate III
    3 years ago

    You should try to add a calculated column instead. Datediff(due date, today(), DAY) should work then. The data type seems to be a date otherwise the datediff function would fail. The wrong numbers I assume is a result of summarising all the projects of equal due date. 

    • Aleksis's avatar
      Aleksis
      Regular Visitor
      3 years ago

      I assume I can't make a calculated column because I'm using a live connection. You mentioned that the wrong numbers are a result of summarising all the projects of equal due date. Is there a way to avoid this?

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    3 years ago

    Hi, Aleksis ;

    First of all, you can't create columns in LC mode, the following is my formula, you can try it.

    Number of days overdue =
SUMX (
    SUMMARIZE (
        'PROJECTS',
        [Due_Date],
        "1", DATEDIFF ( [Due_Date], TODAY (), DAY )
    ),
    [1]
)

    If it doesn't solve your problem, can you share a simple data and hope to output the result? Judging from the pictures, there is no problem with the result returned by your formula? So more information can be provided.


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Aleksis's avatar
      Aleksis
      Regular Visitor
      3 years ago

      Unfortunately, your formula gave the same output as mine so I wasn't able to resolve the issue. Could you please explain more what you mean by "can you share a simple data and hope to output the result"?

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    3 years ago

    Hi, Aleksis ;

    Is your problem solved?  If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Aleksis's avatar
      Aleksis
      Regular Visitor
      3 years ago

      So far, I haven't been able to resolve the issue based on the replies.