Calculating Stage Duration in a Column

[buttercream]

Hello,

Trying to calculate the number of business days between stages.  A SalesID is considered New until it is invoiced.  Any following  instances of "Invoiced" should be labelled Existing and should be calculated separately.  See below table for example.  The fields in red are needed as calculated columns (not measures).

SalesID	Stage	Date	New/Existing	Rank	Duration
10	Entered	4/1/2024	New	1	8
10	Processed	4/10/2024	New	2	13
10	Invoiced	4/26/2024	New	3	0
12	Entered	5/1/2024	New	1	3
12	Processed	5/3/2024	New	2	6
12	Invoiced	5/10/2024	New	3	0
12	Returned	3/1/2024	Existing	1	3
12	Adjusted	3/5/2024	Existing	2	14
12	Invoiced	3/23/2024	Existing	3	0
12	Returned	7/7/2024	Existing	1	1
12	Researched	7/8/2024	Existing	2	3
12	Adjusted	7/10/2024	Existing	3	16
12	Invoiced	7/31/2024	Existing	4	0

[sroy_16]

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.

[rajendraongole1 · Accepted solution]

Hi @buttercream - Create below calcualted column to get the New/Existing

Calculated column:

NewExisting =

VAR FirstInvoiceDate =

    CALCULATE(

        MIN(Stage[Date]),

        Stage[Stage] = "Invoiced",

        ALLEXCEPT(Stage, Stage[SalesID])

    )

RETURN

    IF(

        Stage[Date] <= FirstInvoiceDate,

        "New",

        "Existing"

    )

create another calculated column to bring the rank

Rank =

RANKX(

    FILTER(

        Stage,

        Stage[SalesID] = EARLIER(Stage[SalesID]) &&

        Stage[NewExisting] = EARLIER(Stage[NewExisting])

    ),

    Stage[Date],

    ,

    ASC,

    DENSE

)

calcualte the duration calculated column :

Duration =

VAR CurrentDate = Stage[Date]

VAR NextDate =

    CALCULATE(

        MIN(Stage[Date]),

        FILTER(

            Stage,

            Stage[SalesID] = EARLIER(Stage[SalesID]) &&

            Stage[NewExisting] = EARLIER(Stage[NewExisting]) &&

            Stage[Rank] = EARLIER(Stage[Rank]) + 1

        )

    )

VAR BusinessDays =

    IF(

        NOT ISBLANK(NextDate),

        COUNTROWS(

            FILTER(

                ADDCOLUMNS(

                    CALENDAR(CurrentDate, NextDate),

                    "IsBusinessDay",

                    WEEKDAY([Date], 2) < 6

                ),

                [IsBusinessDay] = TRUE

            )

        ) - 1,

        0

    )

RETURN

    BusinessDays

Replace the table name as per your model.

Hope it works

[buttercream]

Thanks.  It works.