IF AND DATEADD to subtract days from date

[jneuey5]

Hi,

I'm trying to subtract 5 years from a date column based on the criteria of another column. I can't seem to get it right, this is one attempt, Column = IF(AND(sheet1[Next Service Pin] = "5 Year Pin","Y"),DATEADD(sheet1[Next Service Pin Date].[Date],-1825,DAY)). Is there an easier way?

Thanks

Jon

[Anonymous]

First, you should read this: https://dax.guide/dateadd

Then you should know that adding an integer to a date moves the date that many days back or forward since dates are stored under the hood as real numbers where the decimal part is the time of the day.

Ex.: MyTable[Date] + 2 is just the date moved forward by 2 days.

[jneuey5]

Hi, I read over it but it didn't really cover what I'm trying to do. I have a source report that gives me staff and when they will receive a service pin, the source report is messing up the date and giving staff that are due a five year pin a ten year date. I'm trying to get a column with the criteria of 5 year pin next to subtract downt to the 5 year mark without doing anything to the correct dates of 10 year pin and up.

[Anonymous]

"Hi, I read over it but it didn't really cover what I'm trying to do."

It covers what you shouldn't do but you're doing in your formula. Please always remember that the time-intel functions need a proper DATE table to work reliably. A proper date table is described in the article I've linked to above.

For anyone to be able to tell you what you should do to obtain what you want you have to show more about your model than you have so far. I also have a gut feeling that your model consists of one big table... PLEASE DO NOT DO THAT unless you want to produce wrong numbers in no time without even realizing this. You can watch this to see what can happen on such a model:

https://www.sqlbi.com/tv/auto-exist-on-clusters-or-numbers-unplugged-22/

Also, this line from your formula is incorrect (it will return an error):

AND(sheet1[Next Service Pin] = "5 Year Pin","Y")

[jneuey5]

Yes, it is a large data table existing of our employee listing and when they'll receive their next service. The main issue is the source report of which we have no control over. Yes, the formula is incorrect, I've been trying different variations and walk throughs with no sucess. These are the rows being used, the source data is converting staff supposed to get their 5 year pin to 10. I used a basic column to identify 5 year staff with their start date, but getting it to revert their pin date is not working. 

Admin/Staff Office POID Station Location Location Desc Dept ID Dept Desc Cost Center Org Code Empl ID Empl Record Name DateType Used For Calculations Service Dt/Service Pin Elig Dt Current Years of Service Next Service Pin Next Service Pin Date Reports To Position Number Reports To Empl ID Reports To Name