DATEADD Does not work for date 6 months prior, returns blank

[curtismob]

I am trying to calculate the date six months prior to current date, but it does not work.  I created a calculated column as shown below, but blanks are returned.  I have verified that 'Date Time'[Current Date] is current date. 

Date Six Mos Prev = DATEADD('Date Time'[Current Date], -6, MONTH)

'Date Six Mos Prev' and 'Date Time'[Current Date] are both defined with a 'Date' data type and format of (yyyy-MM-dd)

[bblais]

This only works if the corresponding date from 6 months ago is actually in your 'Date Time' table, so it probably is working for dates greater than your earliest date + 6 months.

If it were me, I would do this in the query for the 'Date Time' table and not in a DAX column.  If you edit your query and add a custom column with the following formula it should give you want you want:

= Date.AddMonths([Current Date],-6)

[curtismob]

Hi @bblais,

Thank you for the quick response. 

My 'DateTime' table actually has date information from 5 years previous through current year and I verified 10/12/2016 (six months prior to today) is in the 'DateTime' table.

I am actually trying to create a true/false for dates w/in the last six months to be used in a Visual filter.

I don't have much experience with making query changes, I would rather use DAX, if possible.  If the query change is my only choice, I will also need to create and name the new column, correct?

Thank you,

@curtimob

[v-shex-msft]

Hi @curtismob,

If "DATEADD" not works on your side, you can try to use below formula:

Prev6Month = DATE([date].[Year],[date].[MonthNo]-6,[date].[Day] )

In addition, I'd like to suggest you to check your date column before these operation. If you date field not the date type, you should transform it first.

Regards,

Xiaoxin Sheng

[bblais]

@v-shex-msft, I respectfully disagree with your solution as it would give an error/invalid date for any day before July 1st of any year.

@curtismob, you could try this in your date table to get a bit of 1 for any date in the last 6 months:

isInLast6Months = IF('Date Time'[Current Date] < TODAY(),IF(DATEDIFF('Date Time'[Current Date],TODAY(),MONTH) <= 6,1,0),0)

The first IF is just to handle future dates as the DATEDIFF function doesn't like it when the start date is after the end date.

[curtismob]

Below is what I finally came up with to get the day 1 of the month six months prior.  

I know the 'Date Time'[Current Date].[Day]-'Date Time'[Current Date].[Day] +1 seems redundant, but it seems like I was getting an error if I just entered a 1.

Date 6 Mos Prior Day 1 =

DATE

(

'Date Time'[Current Date].[Year],

'Date Time'[Current Date].[MonthNo] - 5,

'Date Time'[Current Date].[Day]-'Date Time'[Current Date].[Day] +1

)