- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Community Support
- Community Feedback
- DATEDIFF calculation error

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

mbs2016

Regular Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-28-2016
07:45 AM

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.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Ch

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-30-2016
08:50 AM

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!

1 REPLY 1

Ch

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-30-2016
08:50 AM

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!