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
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

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 June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors