cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculating Stage Duration in a Column

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
1 ACCEPTED SOLUTION
Super User

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
)
)
IF(
NOT ISBLANK(NextDate),
COUNTROWS(
FILTER(
CALENDAR(CurrentDate, NextDate),
WEEKDAY([Date], 2) < 6
),
)
) - 1,
0
)
RETURN

Replace the table name as per your model.

Hope it works

 Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

3 REPLIES 3
Resolver II

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

// 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

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.

Super User

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
)
)
IF(
NOT ISBLANK(NextDate),
COUNTROWS(
FILTER(
CALENDAR(CurrentDate, NextDate),
WEEKDAY([Date], 2) < 6
),
)
) - 1,
0
)
RETURN

Replace the table name as per your model.

Hope it works

 Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Frequent Visitor

Thanks.  It works.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors