cancel
Showing results for
Did you mean:
Regular Visitor

## 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Regular Visitor

## Re: 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

3 REPLIES 3
Community Support Team

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

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

## 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.
Highlighted
Regular Visitor

## Re: 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

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 367 members 3,651 guests
Recent signins: