DAX Workout 013 - Calculating Processing Time in Business Days

[tweinzapfel]

Hi Everyone -

“What is our processing time?”

Another common issue that I see is doing analysis on turnaround time. Many situations arise where you have a beginning date (be it an order/sales/start date) and an ending date (completed/delivered, etc.). So naturally a great analysis is monitoring the time in between.

But - what if only business days are wanted? And what if holidays are to be excluded?

So in this workout - your objective is to create a measure to determine the processing time for business days only. In my example I am using Monday - Friday as the standard. However, you may certainly adjust this to your situation.

Note that the picture doesn’t factor in excluding holidays, but I’ll include that in my solution that I’ll post next Sunday.

Below is a dataset of product orders that include a sales date and a delivery date. Feel free to use a date table, a mathematical solution, or any other desired approach. And if you do factor in holidays - be free to add in any additional data that you need.

Workout 013 Data.csv (3.1 KB)

Submission
Load the supplied data file, create your solution, and reply to this post. Upload a screenshot of your solution (or the PBI file) along with any DAX items. Please blur any solution or place it in a hidden section.

[AlexisOlson]

I like to do most of the work in the date table (which any good model should have anyway).

If we have a Holidays table, then we can define a calendar table as

Date table definition

Dates = 
ADDCOLUMNS(
    ADDCOLUMNS (
        CALENDARAUTO(),
        "IsWeekday", WEEKDAY ( [Date], 2 ) < 6,
        "IsHoliday", [Date] IN VALUES ( Holidays[Observed Date] )
    ),
    "IsWorkday", [IsWeekday] && NOT [IsHoliday]
)

Then defining business days to process is a simple calculated column:

Calculated column DAX

Days to Process (Business Days) = 
COUNTROWS (
    FILTER (
        Dates,
        [IsWorkday]
            && Dates[Date] >  Data[OrderDate]
            && Dates[Date] <= Data[DeliveryDate]
    )
) + 0

The average time measure simply averages this calculated column and should be highly efficient even for very large fact tables.

Average time measure DAX

Average Time = AVERAGE ( Data[Days to Process (Business Days)] )

DAX 013 AlexisOlson.pbix (39.1 KB)

[TomiwaB]

Interesting workout @tweinzapfel.

I had to clean the date columns using Text to Columns and Date Function in MS Excel before loading to Power BI.

Kindly find attached my PBI File and see below DAX codes used in my solution.
Tomiwa B_DAX Workout_EDNA.pbix (82.1 KB)

DAX used for creating a Date Table

Date Table = 
            ADDCOLUMNS (
                        CALENDARAUTO (),
                        "Year", YEAR ([Date]),
                        "Quarter", "Q" & QUARTER ([Date]),
                        "Month", FORMAT ( [Date], "mmmm" ),
                        "Month Number", MONTH ([Date]),
                        "Day", FORMAT([Date], "dddd"),
                        "Day No", WEEKDAY([Date], 2))

DAX used for creating the day type column

Day Type = 
            IF('Date Table'[Day No] <= 5, "Business Day", "Weekend")

DAX used in calculating the processing time (days) excluding weekends

Days to Process (Excl. Weekends) = 
                                    CALCULATE(
                                                COUNTROWS('Date Table'),
                                                DATESBETWEEN('Date Table'[Date], 
                                                            'Workout 013 Data'[OrderDate], 
                                                            'Workout 013 Data'[DeliveryDate] - 1), 
                                                            'Date Table'[Day Type] = "Business Day"
                                            )

DAX for Average Processing Time (Days)

Average Time to Process (Days) = 
                                    AVERAGE('Workout 013 Data'[Days to Process (Excl. Weekends)])

Thank you!

[BrianJ]

@tweinzapfel ,

Great, practical workout. I like the “choose your own adventure” aspect of the optional elements.

I chose to pull in holiday dates automatically through an API call in Power Query to Calendarific.com (will post about this tonight…)

@Melissa 's awesome Date table code makes this a piece of cake, since you just point her code to your holiday table and her Dates table updates the IsBusiness Day field automatically to account for weekends and holidays.

Here’s a link to it for anyone who isn’t already currently using it.

Extended Date Table (Power Query M function)

Hi everyone, To make it easier to find I’ve placed the updated Power Query M code function for the extended Date Table / Dates Table in the category where it belongs… I hope you’ll find it useful and if there are any questions, just reach out and let me know! // August 2022 There has been an overhaul of the M code from when it was initially shared. This affects column names and values. In the Analyst Hub (follow this link) you can copy a version of Extended Date table which returns a r…

DAX Script Containing All Measures Used

------------------------------
-- Measure: [Avg process Days]
------------------------------
MEASURE 'Key Measures'[Avg process Days] = 
    AVERAGEX(
        'Data',
        [Business Days]
    )

---------------------------
-- Measure: [Business Days]
---------------------------
MEASURE 'Key Measures'[Business Days] = 
    COUNTROWS(
        FILTER(
            ALL(
                'Dates'[Date],
                'Dates'[IsBusinessDay]
            ),
            'Dates'[Date]
                > SELECTEDVALUE(
                    'Data'[OrderDate]
                )
                && 'Dates'[Date]
                <= SELECTEDVALUE(
                    'Data'[DeliveryDate]
                )
                && 'Dates'[IsBusinessDay] = TRUE
        )
    )
    FormatString = "0"

--------------------------
-- Measure: [Overall Days]
--------------------------
MEASURE 'Key Measures'[Overall Days] = 
    COUNTROWS(
        FILTER(
            ALL( 'Dates'[Date] ),
            'Dates'[Date]
                > SELECTEDVALUE(
                    'Data'[OrderDate]
                )
                && 'Dates'[Date]
                <= SELECTEDVALUE(
                    'Data'[DeliveryDate]
                )
        )
    )
    FormatString = "0"

[Eze]

I did the following in this workout;

1. I cleaned the Order and Delivery dates (NB: some were in wrong date format) using Power Query by splitting the columns (using the \ delimeter) and then created a new column for each of the dates using the Custom Column feature.
2. I used Melissa’s great date table (see the link @BrianJ sent)
3. I Created simple calculated columns for the Overall and Business days
4. Then used the AVERAGE fxn to create a measure for the Avg Process Days.

DAX Code (Business Days)

Summary

DAX Code (Average Process Days)

Summary

@tweinzapfel thanks for the workout.

[Hakeem]

I used a holiday table provided at https://www.generalblue.com/calendar/usa/us-holidays-2023. I like practicing business workday and fiscal calculations because I rarely get the practice at work.

Days to Process = DATEDIFF(SELECTEDVALUE('Workout 013 Data'[OrderDate]),SELECTEDVALUE('Workout 013 Data'[DeliveryDate]),DAY)


Days to Process (Business Days) = 
 
NETWORKDAYS(SELECTEDVALUE('Workout 013 Data'[OrderDate]), SELECTEDVALUE('Workout 013 Data'[DeliveryDate]), 1, VALUES(Holidays[ DATE]) )


Average Time = AVERAGEX('Workout 013 Data', [Days to Process (Business Days)])

[BrianJ]

For those interested, here’s the link to my LinkedIn post tonight with the code for an automated holiday table that will pull the holiday dates for any requested years for over 230 countries and 3,300 states in 100+ languages.

Brian Julius on LinkedIn: #powerbi #powerquery #dax #workouts #mcode...

• Brian

[Cazvon]

Thank you Team EDNA for this opportunity

My first-time taking part in a workout session so here goes!

Action one: Imported my PBI template that included date and key measure tables.

Action two: Transformed date columns into date English (Australian) format using split by delimiter and merge columns capabilities.

Action three: Added New Column containing DAX formula to workout the number of business days between order and delivery dates.

Action five: Created Avg Delivery Weekday measure.

Action six: Card and table visualizations.

Andrivon_Workout013_20230502.pbix (1.3 MB)

Caroline

[hadeeh]

@tweinzapfel

Hi Everyone -

“What is our processing time?”

Another common issue that I see is doing analysis on turnaround time. Many situations arise where you have a beginning date (be it an order/sales/start date) and an ending date (completed/delivered, etc.). So naturally a great analysis is monitoring the time in between.

But - what if only business days are wanted? And what if holidays are to be excluded?

So in this workout - your objective is to create a measure to determine the processing time for business days only. In my example I am using Monday - Friday as the standard. However, you may certainly adjust this to your situation.

Note that the picture doesn’t factor in excluding holidays, but I’ll include that in my solution that I’ll post next Sunday.

Below is a dataset of product orders that include a sales date and a delivery date. Feel free to use a date table, a mathematical solution, or any other desired approach. And if you do factor in holidays - be free to add in any additional data that you need.

Workout 013 Data.csv (3.1 KB)

Submission
Load the supplied data file, create your solution, and reply to this post. Upload a screenshot of your solution (or the PBI file) along with any DAX items. Please blur any solution or place it in a hidden section.

Hello Everyone,

I am a beginner at DAX but just love it! The help provided here and on the Microsoft forums are great help and guidance. I have attached my PBIX file for review.

Step 1: Uploaded the .xlxs table to power bi

Step 2: Uploaded the calendar that I always use (From Avi Singh)

Step 3: Created a holiday table and uploaded

Step 4: Created a custom coloumn for Days to Process (incuding weekends and holidays)

Step 5: Create a invoked function by following the step from here: https://blog.enterprisedna.co/calculate-workdays-between-two-dates-in-power-bi/ that calculated Workdays Using The Power Query.

Step 6: Created the table as per the problem overview

Step 7: Created a DAX for Average workdays

Step 8: Created the Average overall time Card and the product wise average table.

Appreciate all feedback and eager to learn from all of you.

Thank you

Workout013_Solution-Ver1.pbix (165.6 KB)

[Hakeem]

Well this is nice to have.

[mbraun]

Nice little workout for this Friday.

[shahadathossen.shajj]

Here is my submission

DAX Measures

This text will be hidden

Days to process (Overall) =
COUNTROWS (
    FILTER (
        ALL ( 'Date Table' ),
        'Date Table'[Date] > MAX ( 'Workout 013 Data'[OrderDate] )
            && 'Date Table'[Date] <= MAX ( 'Workout 013 Data'[DeliveryDate] )
    )
)

Days to process (Business Days) =
COUNTROWS (
    FILTER (
        FILTER (
            ALL ( 'Date Table' ),
            'Date Table'[Date] > MAX ( 'Workout 013 Data'[OrderDate] )
                && 'Date Table'[Date] <= MAX ( 'Workout 013 Data'[DeliveryDate] )
        ),
        'Date Table'[Day No] IN { 1, 2, 3, 4, 5 }
    )
)

Average Time =
AVERAGEX (
    SELECTCOLUMNS (
        'Workout 013 Data',
        "Business Days", [Days to process (Business Days)]
    ),
    [Business Days]
)