cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mbs2016
Helper I
Helper I

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.

 

1 ACCEPTED SOLUTION
Ch
Frequent Visitor

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

1 REPLY 1
Ch
Frequent Visitor

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

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors