Formula to work out days from Request date to Start date to Completion date

[dd88]

The requirement is to work out and display days from Request date to Start date to Completion date.

The requirement is to display a visual of Request date, and display how many days the request has been sitting in the backlog. Then necessary action can be taken ie change priority and Start date of the request, and assign the task.

What is a formula to acheive this?

TIA

[rajendraongole1]

Hi @dd88 - you can create a calculated column as below, change to your table name and columns :

DaysInBacklog = DATEDIFF('Table'[Request Date], 'Table'[Start Date], DAY)

Create a measure as below:

DaysInBacklogMeasure =
CALCULATE(
SUMX(
'Table',
DATEDIFF('Table'[Request Date], 'Table'[Start Date], DAY)
),
FILTER('Table', NOT(ISBLANK('Table'[Start Date])))
)

By adding the above calculated columns and measures in your visuals, you can easily get the how long each development request has been in the backlog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[dd88]

Thank you that’s great @rajendraongole1 

I used calculated column

DaysInBacklog = DATEDIFF('Tasks Overview'[Request Date], 'Tasks Overview'[Start Date], DAY)

These are the results. I think the formula needs fine tuning.

If Start Date is blank, then  needs to calculate Request Date from Todays Date

Completed Date is being calculated, is a filter to be applied, or added to the expression to remove Completed Date?

Thanks again

[Anonymous · Accepted solution]

@rajendraongole1  Thanks for your contribution on this thread.

Hi @dd88 ,

You can update the formula of calculated column [DaysInBacklog] as below and check if it can return your expected result...

DaysInBacklog =
DATEDIFF (
    'Tasks Overview'[Request Date],
    IF (
        ISBLANK ( 'Tasks Overview'[Start Date] ),
        TODAY (),
        'Tasks Overview'[Start Date]
    ),
    DAY
)

What do you mean for the following sentences? It seems like the field [Completed Date] is a calculated column, what's the calculation logic? Could you please provide some sample data (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

---

Completed Date is being calculated, is a filter to be applied, or added to the expression to remove Completed Date?

---

Best Regards