Reply
Highlighted
Frequent Visitor
Posts: 8
Registered: ‎06-28-2016
Accepted Solution

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)

 

Getting the following error:

A single value for column 'RDOC_SCAN_DATE' in table 'MP_REP_DOCUMENT' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

 


Accepted Solutions
Ch Frequent Visitor
Frequent Visitor
Posts: 2
Registered: ‎06-28-2016

Re: DATEDIFF calculation error

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))

 

Datediff.png

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!

View solution in original post


All Replies
Ch Frequent Visitor
Frequent Visitor
Posts: 2
Registered: ‎06-28-2016

Re: DATEDIFF calculation error

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))

 

Datediff.png

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!