cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Invalid numeric representation of date value: DATEDIFF

Hi 

 

I am trying to calculate 'Resolve Time' for incidents. I worked out the forumula, however the DAX formula is not returning the values for incidents which were open for a longer period of time. 

 

Resolve_Time = DATEDIFF(SUM('Call Request Detail'[OpenDate]), SUM('Call Request Detail'[ResolveDate]), DAY)

 

Below mentioned screenshot represents the  incidents which are opened and closed within 3 days.

 Resolve_time short period of time.PNG

However, if an incident is open for a longer period of time i.e. if an incident is opened on 25/11/2018 and closed on 06/12/2018. The calculation doesn't seem to be working. the follwing error pops out. 

 

Resolve_time Eror.PNG

 

FYI: I am using a direct connection to the DW, hence using the SUM function in my calculation. I understand that we can not access the source columns in DAX formulas when we use direct connection. 

 

Any advice will help me out. 

1 REPLY 1
scottsen
Memorable Member
Memorable Member

The SUM( ) of a date sounds... odd at best.  In the grand total you are *actually* adding dates, which... I'm surprised is even legal 😛 

I would do MIN() for Open and MAX( ) for Resolve... ?

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!