Calculating Stage Duration in a Column

Hello buttercream 

There are two options for doing this. I would provide both the solutions. Try using them and see if they work.

Using advanced Editor in Power Query Mode.

let
Source = ... // your source step,

// Define a function to calculate business days between two dates
BusinessDaysBetween = (StartDate as date, EndDate as date) =>
let
DayCount = List.Count(List.Select(List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)), each not Date.IsInCurrentMonth(_))),
BusinessDays = List.Count(List.Select(List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1, 0, 0, 0)), each Date.DayOfWeek(_, Day.Monday) < 5))
in
BusinessDays,

// Add the "New/Existing" column based on your logic
AddNewExisting = Table.AddColumn(Source, "New/Existing", each if [Stage] = "Invoiced" and [Rank] > 1 then "Existing" else "New"),

// Sort the table by SalesID and Date
SortTable = Table.Sort(AddNewExisting,{{"SalesID", Order.Ascending}, {"Date", Order.Ascending}}),

// Add Rank column
AddRank = Table.Group(SortTable, {"SalesID"}, {
{"AllData", each Table.AddIndexColumn(_, "Rank", 1, 1, Int64.Type)}
}),
ExpandData = Table.ExpandTableColumn(AddRank, "AllData", {"SalesID", "Stage", "Date", "New/Existing", "Rank"}),

// Add Duration column
AddDuration = Table.AddColumn(ExpandData, "Duration", each if [New/Existing] = "Existing" then BusinessDaysBetween([Date], List.Min(Table.SelectRows(ExpandData, each [SalesID] = [SalesID] and [Rank] = [Rank] - 1)[Date])) else null, Int64.Type)
in
AddDuration

Using DAX:

Create a calculated Column:

New/Existing =
IF (
[Stage] = "Invoiced" && [Rank] > 1,
"Existing",
"New"
)

Duration =
VAR CurrentDate = [Date]
VAR PreviousDate =
CALCULATE (
MAX([Date]),
FILTER (
Table,
[SalesID] = EARLIER([SalesID]) && [Rank] = EARLIER([Rank]) - 1
)
)
RETURN
IF (
[New/Existing] = "Existing" && NOT ISBLANK(PreviousDate),
NETWORKDAYS(PreviousDate, CurrentDate),
BLANK()
)

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos.