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.   ...
Need Help
Show and Tell
Tips and Tricks
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