Help with correct Dax forumla for rolling due dates?

[bwill112]

Hey PBI community, 

I am trying to calculate due dates for reports within a calendar table in Power BI. I'm struggling with the dates all calculating correctly and I'm hoping someone with better DAX knowledge can help me make sense of where I'm going wrong!

The due dates that I'm trying to calculate are based on projects with variable date ranges. The due dates I need are based on the end date of the project, but due every year within the scope of the project. These due dates are also either 30, 45, 60, or 90 days after the end date of the project. For example, let's say project "A" starts on 11/30/2018 and ends 12/31/2022 and has a report due 45 days after the "anniversary" of the end date. The upcoming report for this project would be due 02/14/2019. If the report was due 60 days afterwards, it would be due 03/01/2019, and so on. 

I'm currently using DATEADD plus YEAR DIFF to calculate the "anniversary" date. This part is calculating just fine. The problem comes when I try to add the 30, 45, 60, or 90 days to it the anniversary date. This part calculates fine as well until it reaches the end of calendar year 2018 (well, the end of any calendar year). It's still adding the YEAR DIFF to the original date, so it's calculating that the report should be due in 2020, thus completely missing the one still due in 2019. Here's a screenshot as an example: 

Here's the code I've been using: 

---> Date

Date = CALENDAR(DATE(2001,01,01), DATE(2030,12,31))

---> Project Anniversary Date

Project Anniversary Date = DATEADD('Project End Date'[Date],'Project End Date'[Year Diff],YEAR)

--->Year Diff

Year Diff = DATEDIFF('Project End Date'[Date],NOW(),YEAR)

---> Reports Due After 30 Days

30 Days = 'Project End Date'[Project Anniversary Date]+30

(+45, +60,+90, etc.)

Does anyone have any ideas as to how I can fix this? I am not tied to using any particular formulae to calculate the dates. My apologies if something like this has been asked before, but I couldn't seem to find anything! I don't quite have the DAX knowledge to Google search my way around this one.

Any advice is welcome! Thank you!

[v-cherch-msft]

Hi @bwill112

Could you share the .pbix file for us to test?You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.Please explain more about your expected output.Do you want only the year's last day(12/31/2018) should be 02/14/2019(45 days)?If the other values is correct except the underlined value?

Regards,

Cherie

[bwill112]

Cherie,

Here is the link to my pbix report: https://www.dropbox.com/s/cqiphpx5zhfemya/Report%20Due%20Date%20Example.pbix?dl=0 

Let me know if the link doesn't work. 

I need all of the dates that are currently calculating as due in 2020, but have not yet passed in 2019, to be correct. What I underlined was just one of the examples. For example, a project that ended on 12/27/2018 is calculating that a 60 day report is due 02/25/2020, but it should be due 02/25/2019. However, a project that will end on 12/27/2021 will have a 60 day report due on 02/25/2019, but my report is currently missing that due date and only showing 02/25/2020.

Thank you in advance for your time and willingness to help! 

[v-cherch-msft]

Hi @bwill112

It seems you may try to use DATE Function to make the Year value to be 2019.Attached the sample file.

30 Days_2 = DATE(YEAR(NOW()),MONTH('Project End Date'[30 Days]),DAY('Project End Date'[30 Days]))

45 Days_2 = var a='Project End Date'[Project Anniversary Date]+45 return DATE(YEAR(a),MONTH(a),DAY(a))

Regards,

Cherie

[bwill112]

Cherie, 

Thank you very much! I think this will work. 

[bwill112]

I tried to use the suggestions above, but I'm not getting anything still. I've tried for several months to come up with a better solution, but I'm still stuck with report dates that switch back years as soon as the year diff changes.

If anyone has any further insight on this issue, please help!