Reply
Highlighted
Regular Visitor
Posts: 24
Registered: ‎02-13-2018
Accepted Solution

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

Customer IDDiscontinuous Static Transaction Date
A07-07-2016
B08-07-2016
C09-07-2016
D11-07-2016
B12-07-2016
D13-07-2016
A14-07-2016
C16-07-2016
C17-07-2016
A20-07-2016
B21-07-2016
D23-07-2016
B24-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 IDDiscontinuous Static Transaction DateDynamically Changing Day Difference  
A07-07-2016562  
B08-07-2016561  
C09-07-2016560  
D11-07-2016558  
B12-07-2016557 User Input 1
D13-07-2016556 20-01-2018
A14-07-2016555  
C16-07-2016553  
C17-07-2016552  
A20-07-2016549  
B21-07-2016548  
D23-07-2016546  
B24-07-2016545  

 

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


Accepted Solutions
Regular Visitor
Posts: 24
Registered: ‎02-13-2018

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

[ Edited ]

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

 

image.PNG

 

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 Smiley Happy

 

 

View solution in original post


All Replies
Community Support Team
Posts: 4,051
Registered: ‎07-09-2016

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

@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 ) )
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor
Posts: 24
Registered: ‎02-13-2018

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

@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.
Regular Visitor
Posts: 24
Registered: ‎02-13-2018

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

[ Edited ]

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

 

image.PNG

 

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 Smiley Happy