Forum Discussion

bwill112's avatar
bwill112
Frequent Visitor
7 years ago

Help with Report Due Date Calculations

I have a date calculation situation that I am really struggling with and I hope someone can give me some guidance! I need to calculate annual report due dates for reports due a certain number of days (30, 45, 60, and 90) after the anniversary of the start date of the project. For example, if the project started on 12/28/2014, the due date for the report would be on 01/27 (30 days), 02/11 (60 days), etc., of every year after 2014. I also have a report due 90 days before the anniversary of the start date.

 

The calendar table I have now uses DATEDIFF to calculate the year difference between the s/equence of reference dates and the project anniversary date. However when the calendar year changes, so does the year difference, which throws off all of the calculations:

 

For these report due dates, I only need calculations for future dates, not for calendar dates that have already passed.

The reports due 90 days before the start of the project have the opposite problem. Unless I set the year equal to only the year right now, the reports show a due date in the past. 

Where am I going wrong? What am I missing? I’m sure there’s a better way to calculate even the project anniversary date, but I have no idea how. I’ve tried almost every DAX expression combination I could find, but none seem to do the trick. I've asked something similar about this in the past, but with no luck. I will take any suggestion I can get. The example PBI file I've been working with can be found here.

Thanks for your help!