Forum Discussion

mbs2016's avatar
mbs2016
Helper I
10 years ago
Solved

DATEDIFF calculation error

Trying to calculate number of days between two date fields.   Here is what I have,     Days Overdue = DATEDIFF(MP_REP_DOCUMENT[RDOC_SCAN_DATE], (MP_REP_POSTING[RPST_CLEARING_DATE]), DAY)   Gett...
  • Ch's avatar
    10 years ago

    Hi,

     

    Your method is mostly right, but it needs a SUM for the column names to get it to work. I was assuming that you were wanting the number days after a deadline to make it 'Overdue'. I added an image to describe my thinking. 

    I added an extra part in case the end date is met before the deadline. I also added a "*-1" to this but you could just replace the entire second line of the formula with a 0 or whatever you desire.

    This is that calculated measure formula: 

    Days Overdue = IF(SUM(Data[Deadline])>SUM(Data[End Date]),
    DATEDIFF(SUM(Data[End Date]),SUM(Data[Deadline]),DAY)*-1,   
    DATEDIFF(SUM('Data'[Deadline]),SUM('Data'[End Date]),DAY))

     

    If you are trying to just get the days between a start date that is always previous to an end date, it is pretty much what you had before except to add the SUMS. I made a calculated measure for the 'Days from Start to End' with this formula:
    Days from Start to End = DATEDIFF(Sum(Data[Start Date]),SUM(Data[End Date]),DAY)

     

    Now if you only want Monday-Friday to count as part of the days then it is a bit more complicated.

    Hope this helps!