Forum Discussion

mail2vjj's avatar
mail2vjj
Helper III
8 years ago

Date Difference between two random dates

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-154-May-17
23-Dec-1510-May-17
23-Dec-154-May-17
23-Dec-1514-Jun-17
23-Dec-154-May-17
23-Dec-154-May-17
4-Jan-164-May-17
4-Jan-164-May-17
13-Jan-164-May-17
9-Sep-164-May-17
9-Sep-164-May-17
17-Oct-1624-Apr-17
19-Oct-161-Nov-17
21-Oct-1610-Apr-17
21-Oct-1628-Aug-17
21-Oct-1611-Apr-17
24-Oct-1613-Oct-17
11-Nov-1624-Apr-17
9-Feb-171-Nov-17
21-Mar-1710-May-17
22-Mar-172-May-17
30-Mar-172-May-17
5-Apr-171-Jun-17
24-Apr-1715-May-17
2-May-179-May-17
2-May-1716-May-17
3-May-1712-Jul-17
8-May-1710-May-17
9-May-1721-Nov-17
10-May-1715-May-17
12-May-1726-May-17
12-May-171-Jun-17
15-May-1713-Jun-17
1-Jun-179-Jun-17
2-Jun-178-Jun-17
8-Jun-178-Jun-17
9-Jun-179-Nov-17
9-Jun-171-Nov-17
9-Jun-1713-Jun-17
13-Jun-1718-Jul-17
13-Jun-1718-Jul-17
14-Jun-1728-Jul-17
12-Jul-1725-Jul-17
19-Jul-1720-Jul-17
19-Jul-1725-Jul-17
31-Jul-173-Aug-17
28-Aug-1731-Aug-17
21-Sep-1713-Oct-17
21-Sep-1719-Oct-17
26-Oct-1727-Oct-17
26-Oct-1727-Oct-17
27-Oct-173-Nov-17
27-Oct-173-Nov-17
30-Oct-176-Dec-17
31-Oct-1727-Dec-17
1-Nov-171-Nov-17
1-Nov-172-Nov-17
3-Nov-179-Nov-17
6-Nov-1717-Nov-17
10-Nov-1713-Nov-17
21-Nov-1729-Nov-17
22-Nov-1730-Nov-17
23-Nov-1724-Nov-17
23-Nov-1719-Dec-17
23-Nov-1724-Nov-17
24-Nov-1728-Nov-17
24-Nov-1721-Dec-17
30-Nov-1726-Dec-17
5-Dec-1719-Dec-17
5-Dec-175-Dec-17
6-Dec-1719-Dec-17
13-Dec-1727-Dec-17
27-Dec-1728-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

Need Help
Show and Tell
Tips and Tricks

6 Replies

  • v-jianhe-msft's avatar
    v-jianhe-msft
    Resolver II
    8 years ago

    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 

     

    • mail2vjj's avatar
      mail2vjj
      Helper III
      8 years ago

      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