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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors