Date Difference between two random dates

[mail2vjj]

Hi everyone,

I am trying to calculate the difference between two random dates.

I have a table with 2 columns 'buying dates' and 'selling dates' and another table with the Calendar on it.

I am trying to calculate the difference between two dates selected on the slicer, either using Buying Date and Selling Date.

The following is my Buying and Selling Date Table (You can create a calendar based on this table, using CALENDARAUTO).

BUYING DATESELLING DATE

18-Nov-15	4-May-17
23-Dec-15	10-May-17
23-Dec-15	4-May-17
23-Dec-15	14-Jun-17
23-Dec-15	4-May-17
23-Dec-15	4-May-17
4-Jan-16	4-May-17
4-Jan-16	4-May-17
13-Jan-16	4-May-17
9-Sep-16	4-May-17
9-Sep-16	4-May-17
17-Oct-16	24-Apr-17
19-Oct-16	1-Nov-17
21-Oct-16	10-Apr-17
21-Oct-16	28-Aug-17
21-Oct-16	11-Apr-17
24-Oct-16	13-Oct-17
11-Nov-16	24-Apr-17
9-Feb-17	1-Nov-17
21-Mar-17	10-May-17
22-Mar-17	2-May-17
30-Mar-17	2-May-17
5-Apr-17	1-Jun-17
24-Apr-17	15-May-17
2-May-17	9-May-17
2-May-17	16-May-17
3-May-17	12-Jul-17
8-May-17	10-May-17
9-May-17	21-Nov-17
10-May-17	15-May-17
12-May-17	26-May-17
12-May-17	1-Jun-17
15-May-17	13-Jun-17
1-Jun-17	9-Jun-17
2-Jun-17	8-Jun-17
8-Jun-17	8-Jun-17
9-Jun-17	9-Nov-17
9-Jun-17	1-Nov-17
9-Jun-17	13-Jun-17
13-Jun-17	18-Jul-17
13-Jun-17	18-Jul-17
14-Jun-17	28-Jul-17
12-Jul-17	25-Jul-17
19-Jul-17	20-Jul-17
19-Jul-17	25-Jul-17
31-Jul-17	3-Aug-17
28-Aug-17	31-Aug-17
21-Sep-17	13-Oct-17
21-Sep-17	19-Oct-17
26-Oct-17	27-Oct-17
26-Oct-17	27-Oct-17
27-Oct-17	3-Nov-17
27-Oct-17	3-Nov-17
30-Oct-17	6-Dec-17
31-Oct-17	27-Dec-17
1-Nov-17	1-Nov-17
1-Nov-17	2-Nov-17
3-Nov-17	9-Nov-17
6-Nov-17	17-Nov-17
10-Nov-17	13-Nov-17
21-Nov-17	29-Nov-17
22-Nov-17	30-Nov-17
23-Nov-17	24-Nov-17
23-Nov-17	19-Dec-17
23-Nov-17	24-Nov-17
24-Nov-17	28-Nov-17
24-Nov-17	21-Dec-17
30-Nov-17	26-Dec-17
5-Dec-17	19-Dec-17
5-Dec-17	5-Dec-17
6-Dec-17	19-Dec-17
13-Dec-17	27-Dec-17
27-Dec-17	28-Dec-17

These are what my 2 slicers look like:

The Buying Date is set to 'After' and the Selling Date is set to 'Before'

So what I want is depending upon the Buying and Selling dates selected in the slicer, I want to calculate the difference of days between those dates.

I have tried to use DATEDIFF but the formula asks for a specific date, so I tried to use the MIN and Max functions along with it but it did not really work.

So if you can come up with anything, please let me know.

Thank you,

Vishesh Jain

[v-jianhe-msft]

Hi,

May I know how is your issue going currently?

BR,

Henry 

[mail2vjj]

Hi Henry,

Sorry I didn't get a change to look at your solution.

I am still working on it and will let you know if your solution works, or if I find one of my own.

Thank you,

Vishesh Jain

[v-jianhe-msft]

Hi,

Thank you for the notification.

BR,

Henry 

[mail2vjj]

Hey Henry,

I tried your method but it doesn't seem to be working.

From whatever I know about PowerBI and DAX, your solution should have worked.

Here is the screen shot of my result.

Also, I am looking for a scalar value, not really planning to use this measure as a column in any of the tables.

I am still working on it and will let you know if I come up with a solution.

Anyways, thanks for your help and my apologies for not being able to reply sooner.

Thank you,

Vishesh Jain

[v-jianhe-msft]

Hi,

Thank you for your notification. 

[v-jianhe-msft]

Hi,

So you hope to calculate the date difference between two dates in slicer, right? To achieve it, we need to create two new tables (Buying date table and Selling date table), which contains all the dates from 18-Nov-15 to 27-Dec-17, 4-May-17 to 28-Dec-17, accordingly. Otherwise, the date can only be the one which exists in your original dataset.

Below are my steps:

1. Create Buying Date table using DAX: (“Table1 “ is your original table )

Tablebuying = CALENDAR(FIRSTDATE(Table1[Buying Date]),LASTDATE(Table1[Selling Date]))

Similarly, create Selling Date table using

Tableselling = CALENDAR(FIRSTDATE(Table1[Selling Date]),LASTDATE(Table1[Selling Date]))

2. Make them as slicer, after/ before,etc.

3. Add a measure to calculate the date difference:

Measure 3 = DATEDIFF(FIRSTDATE(Tablebuying[Date]),LASTDATE(Tableselling[Date]),day)

4. When previewing, you could get result of date difference:

Best Regards,

Henry