Date Issue

njsimmons82​ 

If you want resolution days per ticket, make it a calculated column instead:

Resolution Days =DATEDIFF('June 1 to Aug 19'[Created Date],'June 1 to Aug 19'[Resolved Date],DAY)

Hi njsimmons82​ 

Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.

Hi njsimmons82​ 

If you want Resolution Days per ticket as row by row then create Calculated Column instead Measure

Resolution Days =

DATEDIFF(

'June 1 to Aug 19'[Created Date],

'June 1 to Aug 19'[Resolved Date],

DAY)

In calculated column DAX evaluates it row by row, so there is an implicit row context and each row's own Created or Resolved Date is used with no error

If you want a measure then you need to use column in an aggregation as measure has to view many rows into one number

Avg Resolution Days =

AVERAGEX(

'June 1 to Aug 19',

DATEDIFF(

'June 1 to Aug 19'[Created Date],

'June 1 to Aug 19'[Resolved Date],

DAY))

AVERAGEX or SUMX iterates row by row internally computing DATEDIFF for each row and aggregates the results

njsimmons82​ 

The error occurs because you’re creating a measure, but [Created Date] and [Resolved Date] contain multiple rows. DAX needs an aggregation or a single selected value.

maybe you can try this

Resolution Days =

VAR CreatedDate =

SELECTEDVALUE('June 1 to Aug 19'[Created Date])

VAR ResolvedDate =

SELECTEDVALUE('June 1 to Aug 19'[Resolved Date])

RETURN

IF(

NOT ISBLANK(CreatedDate) &&

NOT ISBLANK(ResolvedDate),

DATEDIFF(CreatedDate, ResolvedDate, DAY)

)

if this does not work, pls provide your sample data and expected output.

Hi,

You will have to warp that column with a function such as min().

If the goal is to calculate the duration for each record, The 'Resolution Days' column with DATEDIFF() calculated column should be created in 'June 1 to Aug 19' table, not the Calendar table.