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.
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:
Please let me know if I have misunderstood.
Hi Anonymous ,
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!
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.This works! Thanks again for all of your help! Great Job!