Calculating Sum between 2 dates excluding weekends

[Grimfandango227]

I am trying to calculate an outstanding quantity between Today and a specific date to figure out demand but want to exclude weekends from this. Currently using this but it treats Sat. and Sun. as actual days thus screwing things up. 

Is there a quick method for telling a measure to exclude weekends?

Thanks! Any help would be much appreciated. 

[transform99]

Looping back I realise we haven't actually answered your question.

Yes there is a quick method for telling a measure to exclude weekends. All of the below has commentary in it and you should be able to paste it straight into PowerBI

Create a helper column:

Weekday =
//  Determine if weekday or weekend. '2' starts the week on Monday making Saturday/Sunday represent integers 6 and 7 based on DAX formula.

IF (
    WEEKDAY ( [Due_Date], 2 ) > 5,
    0,
    1
)

Write a SUMX formula where we filter the values to between the declared StartDate and EndDate. We can also feed it the helper column to exclude weekends.

Production = 
//  Declare Start Date
VAR StartDate =
    TODAY ()

//  Declare End Date
VAR EndDate =
    TODAY () + 7

//  Sum the values of [Sum of Remaining Qty] but filtering the Due date to the StartDate /EndDate variable conditions. Exlude Weekends
RETURN
    SUMX (
        FILTER (
            'Table',
            'Table'[Due_Date] >= StartDate
                && 'Table'[Due_Date] <= EndDate
                && 'Table'[Weekday] = 1
        ),
        'Table'[Sum of Remaining Qty]
    )

(I am in Australia, so the standard date format is defaulted to DD/MM/YYYY)

If you want this to be dynamic - use the following formula, and put a slicer on the page for Due Date:

Production = 
//  Declare Start Date
VAR StartDate =
    MIN ( [Due_Date] )

//  Declare End Date
VAR EndDate =
    MAX ( 'Table'[Due_Date] )

//  Sum the values of [Sum of Remaining Qty] but filtering the Due date to the StartDate /EndDate variable conditions. Exlude Weekends
RETURN
    SUMX (
        FILTER (
            'Table',
            'Table'[Due_Date] >= StartDate
                && 'Table'[Due_Date] <= EndDate
                && 'Table'[Weekday] = 1
        ),
        'Table'[Sum of Remaining Qty]
    )

[v-zhouwen-msft]

@Ashish_Mathur @amitchandak Thank you for your quick replies,I have one other thought to share.

Hi @Grimfandango227 ,

I've assumed some data, the table data is shown below:

You can create an additional date table to determine if the date is a weekend or not, please follow these steps:
1. Use the following DAX expression to create a table

Table = CALENDAR(DATE(2024,1,1),DATE(2024,12,31)) 

2. Use the following DAX expression to create a column named ‘IsWeekend’

IsWeekend = IF(WEEKDAY('Table'[Date],2)>5,TRUE(),FALSE())

The data of the created table is as follows:

The relationship is as follows:

3. Use the following DAX expression to create a measure

Measure = 
VAR StartDate = TODAY()
VAR EndDate = TODAY() + 5
RETURN 
CALCULATE(SUM('DOMO_Sales_Line'[Outstanding]),'Table'[Date] >= StartDate && 'Table'[Date] <= EndDate,'Table'[IsWeekend] = FALSE())

4. Final output

360 = (10+20+30+40+50+60+70+80)

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Grimfandango227]

Good Afternoon, 

So I was able to follow all the steps you listed above (Thanks for that) but it would appear that weekend days are still being counted within the calculation. I'll show some data below for example. 

1. Let's take this specific Item for example 

Theoretically by using your formula looking out 7 days from today, we should have a total of 2,700 because today +7 days (excluding weekends) should give us that total. 

Here is my finished formula 

Unfortunately, it looks like it is still counting Sat. and Sunday when looking out 7 days. 

If you see any errors in my work here please let me know and any help would be greatly appreciated. 

Thanks!

[v-zhouwen-msft]

Hi @Grimfandango227 ,

Depending on the expression you provided, due to the possible time difference between us. I am assuming that the date provided by the Today() function is March 5th.
Start date: 3/5/2024
End date: 3/12/2024
Therefore, the data for March 14 will not be counted. If yours needs to include today's date:

VAR EndDate = TODAY() + 6

Please let me know if I have misunderstood.

[Grimfandango227]

Hi @v-zhouwen-msft ,

You were correct on the start date. So lets use today now. I need the end date to reflect +7 business days and bypass Sat. and Sun. thus giving me data on 3/14/24. 

3/6 - Wed. - 1

3/7 - Thurs - 2

3/8 - Fri - 3

3/9 - Sat - OMIT 

3/10 - Sun - OMIT

3/11 - Mon - 4

3/12 - Tues - 5

3/13 - Wed - 6

3/14 - Thurs - 7

I refreshed the report today and it is still now counting the data for next Wed. (See Below)

Current data ↓↓

Current expression ↓↓

Current Output↓↓

The correct answer should be 6600.

Does that make sense?

Thanks!

[v-zhouwen-msft · Accepted solution]

Hi @Grimfandango227 ,

I'm sorry I misunderstood you before, your requirement is to calculate the value of 7 consecutive working days, for not the value of 7 consecutive days that are working days. Let's start again.

My date today is March 7th.

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a table named ‘Table’

Table = CALENDAR(DATE(2024,1,1),DATE(2024,12,31)) 

2. Use the following DAX expression to create a column named ‘IsWeekend’ in ‘Table’

IsWeekend = IF(WEEKDAY('Table'[Date],2)>5,TRUE(),FALSE())

3. Use the following DAX expression to create a table named ‘Table2’

Table 2 = FILTER('Table','Table'[IsWeekend] = FALSE())

4. Use the following DAX expression to create a column named ‘Column’ in ‘Table2’

Column = COUNTROWS('Table 2') - RANKX(ALL('Table 2'), 'Table 2'[Date],,DESC) + 1

5. Use the following DAX expression to create a table named ‘Table3’

Table 3 = FILTER('Table 2','Table 2'[Date] >= TODAY() )

6. Use the following DAX expression to create a table named ‘Table4’

Table 4 = FILTER('Table 3','Table 3'[Column] = MINX('Table 3',[Column] + 6))

7. Use the following DAX expression to create a measure named ‘SevenWorkDay’

SevenWorkDay = 
VAR StartDate = TODAY()
VAR EndDate = MAXX(FILTER('Table 3','Table 3'[Column] = MINX('Table 3',[Column] + 6)),[Date])
RETURN 
CALCULATE(SUM('DOMO_Sales_Line'[Outstanding]),'Table'[Date] >= StartDate && 'Table'[Date] <= EndDate,'Table'[IsWeekend] = FALSE())

8. The model relationships are as follows:

9. Fianl output

I've uploaded my test file so you can check it out.

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Grimfandango227]

This works! Thanks again for all of your help! Great Job!

[transform99]

Hi Grimfandango227,

I would recommend you look at amitchandak's answer to the question. This should resolve your query.

Networkdays is the standard function in Excel to determine business days between 2 dates. This should be able to help identify the # of days and was added to Power BI in ~July 2022.

It's important to note that Networkdays is slightly different to Workday. Workday calculates the # of days exclusive of the start date, whilst Networkdays is inclusive of the start date.

In excel this would mean you write "NETWORKDAYS([start date], [end date], [omit holidays])-1" to remove the extra day. The formula does stipulate it is inclusive on the documentation page, but give it a try and see if it comes out correct:
NETWORKDAYS function (DAX) - DAX | Microsoft Learn

[Ashish_Mathur]

Hi,

Create a Calendar Table with a calculated column titled as DoW.  The formula in this column should be =weekday(Calendar[Date],2).  Use this measure pattern:

Measure = calculate(sum(Data[sales]),datesetween(Calendar[Date],today(),today()+5),calendar[Dow]<=5)

Hope this helps.

[amitchandak]

@Grimfandango227 , yes networkdays function

calculate(networkdays(min(Table[Start Date]), max(Table[End Date]), 1)

AverageX(Table, networkdays([Start Date], [End Date],1) )

Power BI - Business day with and without using DAX Function NETWORKDAYS: https://www.youtube.com/watch?v=Qs03ZZXXE_c
https://medium.com/@amitchandak/power-bi-dax-function-networkdays-5c8e4aca38c