DatesInPeriod problem with certain date fields but not other date fields

[Frenchtom811]

Hi Folks - I am trying to created a calculated field with the DatesInPeriod function.  The field calculation in the most basic form is:

Total Revenue PreLaunch = CALCULATE(SUM('Table'[Net Revenue]), DATESINPERIOD('Table'[Created_Date], 44104, -30, DAY))

44104 = September 30, 2020.  When I run the above calculation I get the following error:

A date column containing duplicate dates was specified in the call to function 'DATESINPERIOD'

Referring to the below screen grab, if I substitute [Created_Date] (shaded in grey) with [Start_Date] or [Expiration_Date] (circled in red) the calculation works.  If you look at the below screen grab, [Created_Date], [Start_Date] and [Expiration_Date] appear to have the same date formatting and all fields have duplicate values.  Why does the calculated field work with [Start_Date] and [Expiration_Date] but gives me a duplicate date error when using [Created_Date]?  BTW, the [Start Date] and [Expiration Date] have various values, the screen grab is a small sample and only shows a single value for each field.   

[amitchandak]

@Frenchtom811 , You have to date table and why are you using the number in place of date. Power bi might not so implicit conversion

Total Revenue PreLaunch = CALCULATE(SUM('Table'[Net Revenue]), DATESINPERIOD('Table'[Created_Date], date(2020,09,30), -30, DAY))

example

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Frenchtom811]

Hello @amitchandak and thanks for your reply.  The DatesInPeriod syntax is DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>).  I can use a number, a date, a date table, etc. for the <start_date> or the <number_of_intervals> and everything works perfectly.  However, the calculation only works when the <dates> value is set to my Start_Date or Expiration_Date field.   The calculation does not work with my Created_Date field even though the Created_Date field is formatted the same as my Start_Date and Expiration_Date.  Any idea why this is happening?

[AlB]

Hi @Frenchtom811 

Don't use time intelligence function on columns in your fact table. Use a proper, complete date table. Time intelligence is only guaranteed to function properly when operating on date columns that have complete years

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers