datediff and a slicer

[kris_talikowski]

Hi

I have a table Visits with Customer ID (Customer ID FK] and multiple customer visits with visit date (Start Date).

This measure works beautifully when there is no date slicer on the report. It brings number of days between customer's first visit date and today.

CustomerRelationship = SUMMARIZE(Visits,Visits[Customer ID FK], "Relationship",DATEDIFF(min(Visits[Start Date]),TODAY(),DAY))

Now, I would like the users to have an option to select a date instead of using Today. I want to calculate first visit's date against selected date on the slicer.

This measure works but it always brings the value of 1 because min(Visits[Start Date) is taken from the slicer. So, there is just one day difference (or 0 depending on date format) between min date and selected date.

CustomerRelationship = SUMMARIZE(Visits,Visits[Customer ID FK], "Relationship",CALCULATE(DATEDIFF(min(Visits[Start Date]), SELECTEDVALUE('Date'[Month Year]),DAY)))

Any ideas? You help would be appreciated.

[kris_talikowski]

Thaanks amitchandak.

I applied this but the message I have now is, the expression specified in the query is not a valid table expression.

CustomerRelationship = AverageX(

SUMMARIZE(Visits,Visits[Customer ID FK], "_Relationship",DATEDIFF(min(Visits[Start Date]),selectedvalue(TableDate[Day Date]),DAY)), [_Relationship])

[amitchandak]

@kris_talikowski , Create a measure and use that with customer from original table

AverageX(

SUMMARIZE(Visits,Visits[Customer ID FK], "_Relationship",DATEDIFF(min(Visits[Start Date]),selectedvalue(Date[Date]),DAY)), [_Relationship])

You can use SUMX inplace of Averagex or You can use maxx(allselected(Date), Date[Date])  in place of selectedvalue(Date[Date])