Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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 ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |