Forum Discussion
How to get difference between static date column and dynamic date (input by user)?
| 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.
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:
3 Replies
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 ) )- 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.
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:
