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

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 :smileyhappy: