cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bwill112 Frequent Visitor
Frequent Visitor

Help with correct Dax forumla for rolling due dates?

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: 

 

Incorrect Due Date Example.JPGIncorrect Due Date Example (2).JPG

 

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!

 

5 REPLIES 5
v-cherch-msft Super Contributor
Super Contributor

Re: Help with correct Dax forumla for rolling due dates?

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
bwill112 Frequent Visitor
Frequent Visitor

Re: Help with correct Dax forumla for rolling due dates?

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 Super Contributor
Super Contributor

Re: Help with correct Dax forumla for rolling due dates?

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
bwill112 Frequent Visitor
Frequent Visitor

Re: Help with correct Dax forumla for rolling due dates?

Cherie, 

 

Thank you very much! I think this will work. 

bwill112 Frequent Visitor
Frequent Visitor

Re: Help with correct Dax forumla for rolling due dates?

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!

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 301 members 3,163 guests
Please welcome our newest community members: