How to get difference between static date column and dynamic date (input by user)?

[bizbi]

Customer ID	Discontinuous Static Transaction Date
A	07-07-2016
B	08-07-2016
C	09-07-2016
D	11-07-2016
B	12-07-2016
D	13-07-2016
A	14-07-2016
C	16-07-2016
C	17-07-2016
A	20-07-2016
B	21-07-2016
D	23-07-2016
B	24-07-2016

User inputs a Reporting Date (through a slicer or if there's any other better way, please let me know) from a range of dates like 1 Jan 2016 to 31 Dec 2040. Expected output is the Dynamically Changing Day Difference:

Customer ID	Discontinuous Static Transaction Date	Dynamically Changing Day Difference
A	07-07-2016	562
B	08-07-2016	561
C	09-07-2016	560
D	11-07-2016	558
B	12-07-2016	557		User Input 1
D	13-07-2016	556		20-01-2018
A	14-07-2016	555
C	16-07-2016	553
C	17-07-2016	552
A	20-07-2016	549
B	21-07-2016	548
D	23-07-2016	546
B	24-07-2016	545

Similarly, day difference column should be updated each time user selects a different date.

[bizbi · Accepted solution]

One possible way to achieve this can be using a predefined day difference column.

First, define two tables:

Table 1: Date Input by User = CALENDAR(DATE(2015,1,1),DATE(2025,12,31))

Table 2: Static Date = SAMPLE(15,CALENDAR(DATE(2016,1,1),DATE(2025,12,31)),[Date],ASC) #This formula is just for reference

Then define a common table which is the cross join of the above two tables.

Common Table = CROSSJOIN('Table 1: Date Input by User','Table 3: Only distinct dates from Table 2')

#Table 3 can be made in Query Editor by duplicating Table 2 and removing duplicates in the column of dates (Right Click on Column Header=>Remove Duplicates)

Next, define the relationships as follows (Please ignore everything other than Date 1, 2 and 3 as they were added for my internal testing):

The desired pre-defined calculated column is:

Day Difference = DATEDIFF('Common Table'[Date 1],'Common Table'[Date 3],DAY)

Now just put a slicer on Date 1 in report for getting input from user. Next, add a table containing 'Date 2: Static Date' and 'Day Difference'. Any Date 1 selection will give the required day difference.

Hope this helps 

[v-chuncz-msft]

@bizbi,

You may add a measure as follows.

Measure =
VAR d1 =
    SELECTEDVALUE ( Table1[Discontinuous Static Transaction Date] )
VAR d2 =
    SELECTEDVALUE ( 'Calendar'[Date] )
RETURN
    IF ( d2 <= d1, - DATEDIFF ( d2, d1, DAY ), DATEDIFF ( d1, d2, DAY ) )

[bizbi]

@v-chuncz-msft Thanks for the reply. I understand what is being done here but the problem in my case is that the dates are coming from two separate tables both of which may have repeating values. So, SELECTEDVALUE is giving me a BLANK.