Dax date variables

[pkoel-asb]

Hello All,

   I am trying to create a measure that will tell me the difference between two dates for a Credit that a user has filtered on. Here is my code:

DaysbetweenAssgined&Claimed = 
  var Startdate = if(CONTAINSSTRING('SpecConnect History'[Event],"Credit Awarded"),'SpecConnect History'[TimeStamp])
  Var Enddate = if(CONTAINSSTRING('SpecConnect History'[Event],"Customer Survey Submitted"),'SpecConnect History'[TimeStamp])
  return
      DATEDIFF((Startdate,Enddate,days)

I do not think I am doing my If's correctly. Any suggestions?

Cheers!

Peter

[amitchandak]

@pkoel-asb , in this case quite a few dates would be blank ,

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[pkoel-asb]

Hello,

   Thank you for the reply. Here is an image:

[pkoel-asb]

Hello,

I have been able to create a test measure below:

Days From Assign to Customer Survey =

VAR StartDate =

SUMMARIZECOLUMNS (

'SpecConnect History'[TimeStamp],

KEEPFILTERS (

TREATAS ( { "Credit Awarded (via assignment)" }, 'SpecConnect History'[Event] )

),

KEEPFILTERS ( TREATAS ( { 27 }, 'SpecConnect History'[CreditID] ) )

)

VAR EndDate =

SUMMARIZECOLUMNS(

'SpecConnect History'[TimeStamp],

            KEEPFILTERS(

            TREATAS( {"Customer Survey Submitted"}, 'SpecConnect History'[Event] )

        ),

        KEEPFILTERS( TREATAS( {27}, 'SpecConnect History'[CreditID] ))

)

Var mDays=DATEDIFF(StartDate,EndDate,day)

return mDays

In the example, I used the query generator in DaxStudio. I had to hard code in a value for the Credit Id (27) in the example above. I want to get the value a user selected from a Slicer visual. How do I get that value into the Dax calculation?

Cheers!

Peter