Afternoon,
I'm really hoping someone can assist with the formula/calculation to insert a date when one date is missing between the two dates I'm trying to basis the calculation on.
My table [fCompletedPRs] contains information related to procurement requisitions submitted and worked in our system. Part of the data contains dates; Column Names: "Final Approval Date", "Accepted Date" and "Completed Date". I also have a date table that calculates "working day" (0,1).
In general, most records contain a date in each one of these columns, so I'm able to calcuate the # of business days without any issue using the following DAX formula.
=CALCULATE(SUM(dCalendar[WorkingDays]),DATESBETWEEN(dCalendar[Date],fCompletedPRs[Final Approval Date],fCompletedPRs[Completed Date]))
In this formula, I'm calculating the number of work days between "Final Approval Date" and "Completed Date"
My issues is that some records do not contain a "Final Approval Date", but all records do contain an "Accepted Date".
So, what I'm hoping to do, is that when the "Final Approval Date" is blank, insert the "Acceptance Date", so it will then calculate the # of working dates.
Basically, looking to calculate the # of days between two dates and when one of those dates is missing, insert a date, and then do the calculation on those dates.
I would greatly appreciate any help, as I have been plugging away at this all day long.
Thank You
I'm really hoping someone can assist with the formula/calculation to insert a date when one date is missing between the two dates I'm trying to basis the calculation on.
My table [fCompletedPRs] contains information related to procurement requisitions submitted and worked in our system. Part of the data contains dates; Column Names: "Final Approval Date", "Accepted Date" and "Completed Date". I also have a date table that calculates "working day" (0,1).
In general, most records contain a date in each one of these columns, so I'm able to calcuate the # of business days without any issue using the following DAX formula.
=CALCULATE(SUM(dCalendar[WorkingDays]),DATESBETWEEN(dCalendar[Date],fCompletedPRs[Final Approval Date],fCompletedPRs[Completed Date]))
In this formula, I'm calculating the number of work days between "Final Approval Date" and "Completed Date"
My issues is that some records do not contain a "Final Approval Date", but all records do contain an "Accepted Date".
So, what I'm hoping to do, is that when the "Final Approval Date" is blank, insert the "Acceptance Date", so it will then calculate the # of working dates.
Basically, looking to calculate the # of days between two dates and when one of those dates is missing, insert a date, and then do the calculation on those dates.
I would greatly appreciate any help, as I have been plugging away at this all day long.
Thank You
