Forum Discussion
Calculating Sum between 2 dates excluding weekends
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) + 15. 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.
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]
)