Forum Discussion
DateAdd() math cutting off at year mark
OK. I have a calculated column on my Date table. I want to know if any particular date is within the last 365 days of today, or the previous 365 days. I think this should give it to me. Looking at this today, it should return CY365 for any date between 6/25/2017 and 6/24/2018. (And future dates, but not relevant. here)
Instead, what I get is dates between 6/25/2017 and 12/31/2017. PY365 shows for dates between 6/25/2016 and 12/31/2017.
Take the date, Add 365 days to it, and if the result is later than today, then that date is within 365 days of today. Right?
CYisCurrent365 =
IF (
DATEADD ( 'Date'[Date], +365, DAY ) >= TODAY (),
"CY365",
IF ( DATEADD ( 'Date'[Date], +730, DAY ) >= TODAY (), "PY365", BLANK () ))
What is wrong with my thinking here?
Phil
Hi,
Does this work?
=IF('Date'[Date]>=TODAY()-365,"CY365",IF('Date'[Date]>=TODAY()-730,"PY365",BLANK()))
Hope this helps.
3 Replies
Hi,
Does this work?
=IF('Date'[Date]>=TODAY()-365,"CY365",IF('Date'[Date]>=TODAY()-730,"PY365",BLANK()))
Hope this helps.
Sorry for the delay. Other projects were taking precedence. Yes, this does work. Thank you.
Do you have an explanation as to why my formula did not work? Problem solved, but the mystery still exists.
In either case, thank you.
Phil
Hi psmith-nhs-inc,
Have you solved your problem?
If you have solved, could you share your solution or always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please share your data sample and your desired output, so that we can help further investigate on it?
Best Regards,
Cherry
