Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Grimfandango227
Frequent Visitor

Calculating Sum between 2 dates excluding weekends

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. 

 

Grimfandango227_0-1709573527854.png

 

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

 

Thanks! Any help would be much appreciated. 

1 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:

vzhouwenmsft_0-1709790359509.png

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())

vzhouwenmsft_1-1709790421501.png

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

vzhouwenmsft_2-1709790479711.png

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

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

vzhouwenmsft_3-1709790531065.png

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

vzhouwenmsft_4-1709790555765.png

vzhouwenmsft_5-1709790563536.png

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:

vzhouwenmsft_6-1709790604304.png

9. Fianl output

vzhouwenmsft_7-1709790617815.png

 

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.

View solution in original post

10 REPLIES 10
transform99
Resolver I
Resolver I

 

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]
    )

 

 

 

transform99_2-1709863220656.png

 

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

 

transform99_1-1709863188259.png

 

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]
    )

 

 

transform99_3-1709863533615.png

 

v-zhouwen-msft
Community Support
Community Support

@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:

vzhouwenmsft_0-1709626810833.png

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:

vzhouwenmsft_1-1709626885240.png

 

 

The relationship is as follows:

vzhouwenmsft_2-1709626902590.png

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

vzhouwenmsft_3-1709627001054.png


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.

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 

Grimfandango227_0-1709677780220.png

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 

 

Grimfandango227_1-1709677921723.png

 

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

Grimfandango227_2-1709677979227.png

 

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

 

Thanks!

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.

 

 

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

Grimfandango227_0-1709734708979.png

Current expression ↓↓

Grimfandango227_1-1709734758706.png

Current Output↓↓

Grimfandango227_2-1709734793678.png

 

The correct answer should be 6600.

 

Does that make sense?

 

Thanks!

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:

vzhouwenmsft_0-1709790359509.png

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())

vzhouwenmsft_1-1709790421501.png

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

vzhouwenmsft_2-1709790479711.png

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

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

vzhouwenmsft_3-1709790531065.png

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

vzhouwenmsft_4-1709790555765.png

vzhouwenmsft_5-1709790563536.png

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:

vzhouwenmsft_6-1709790604304.png

9. Fianl output

vzhouwenmsft_7-1709790617815.png

 

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.

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

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
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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