cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mbs2016 Regular Visitor
Regular Visitor

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

Accepted Solutions
Highlighted
Ch Frequent Visitor
Frequent Visitor

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

1 REPLY 1
Highlighted
Ch Frequent Visitor
Frequent Visitor

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 206 members 2,194 guests
Please welcome our newest community members: