Calculate Days between columns on different tables and a Selected value

[Anonymous]

Hi All

I would like to calculate the days between three column dates that are in two different tables as a measure.

The date to be selected in a slicer is called FullDate and is on the date table DIM Time
The dates DateFinalised and DateAdded is on a table named DIM Claims

DIM Claims has unique rows for each claim number/reference. One row per claim number/reference.

Can somebody given a solution please?

And to take it one step further: 

If the DateFinalised is 3000 01 01 (being unfinalised) then the days should be dateselected DIM Time Full Date minus DIM Claims Date Added

If the date is finalised (not 3000 01 01) and the date finalised is earlier than date selected then the days should be DIM Claims Date Finalised minus DIM Claims Date Added 
otherwise if the date finalised is (not 3000 01 01) and the date finalised is after the date selected then the days should be DIM Time Date Selected minus Date Added

[V-pazhen-msft · Accepted solution]

@Anonymous 

Based on your condition, try the following switch measure:

Measure = SWITCH(TRUE(),
sum(DIM Time[DateFinalised])=Date(3000,01,01), DateDiff(Selectedvalue(DIM TIme[FullDate]), sum(DIM Claims[DateAdded]),DAY),
sum(DIM Time[DateFinalised])<=SELECTEDVALUE(DIM TIme[FullDate]), DateDiff(SUM(DIM Claims[DateFinalised]),SUM(DIM Claims[Date Added]),DAY),
sum(DIM Time[DateFinalised])>SELECTEDVALUE(DIM TIme[FullDate]), DateDiff(Selectedvalue(DIM TIme[FullDate]), sum(DIM Claims[DateAdded]),DAY))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Anonymous]

A sample file would be most helpfull.

I think its somewhere along the line of

IF( MAX(DateFinalised) = DATE(3000,01,01)

   ,DATEDIFF(DAY,'DIM Time'[Full Date],'DIM Claims'[Date Added])

  ,DATEDIFF(DAY,'DIM Claims'[Date Finalised],'DIM Claims'[Date Added])

   )

But depending on you data model you have to stuff some max (or min) in