How to find date periods between 2 dates?

Question

I have 2 dates one is stored inside my date and for other date I am using calculated column in order to store the end date into that, how an I calculate the difference in time period between those dates, I need the date period between all those dates is that possible with DAX?

How can I use calculated column inside my DAX and also I dont have a calender table inside my database.

2019-05-31 and end date is 2019-06-03 then the difference will give me 3 dates that is 2019-05-31,2019-06-01 2019-06-02 and 2019-06-03

Answer 1

Totally possible and easy. If you just need the difference between dates in two columns you can create a calculated column using the following:

DateDiff =
DATEDIFF ( 'Table'[Date1], 'Table'[Date2], DAY )

This will take the difference between Date1 and Date2 in days.

Answer 2

DECLARE @start_date [date] = CAST(‘2012-08-01’ as [date])
DECLARE @end_date [date] = CAST(‘2012-09-01’ as [date])
SELECT
DATEADD(day, [v].[number], @start_date)
FROM
[master].[dbo].[spt_values] [v]
WHERE
[v].[type] = ‘P’ AND
DATEADD(day, [v].[number], @start_date) <= @end_date