Forum Discussion
calculations excluding weekends
Hi All
I was trying something but failing to understand the logic.
I have one date table and another master table which has the data. Connected with date columns (1 to many)
I want to do calculations based on day name. i.e. if its Monday, I want to take a count or a sum for last three days excluding Saturday and Sunday. which will be Wed +Thurs + Fri.
If it is tuesday then sum = Thurs + Fri + Mon
and so on.
Any logic that I can use here or more information if you all need?
Hi Anonymous ,
I created some data:
Main Table:
Date:
Here are the steps you can follow：
1. Create calculated column.
weekday = WEEKDAY('MainTable'[Date],2)workday = IF( 'MainTable'[weekday] in {6,7},0,1)Index = RANKX(FILTER(MainTable,'MainTable'[workday]=1),[Date],,ASC,Dense)Sum_3day = CALCULATE(SUM('MainTable'[amount]),FILTER(ALL(MainTable),'MainTable'[Index]>=EARLIER(MainTable[Index])+2&&'MainTable'[Index]<=EARLIER(MainTable[Index])+4))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
6 Replies
- Greg_Deckler
Community Champion
Anonymous Right, so it is similar to Net Work Days: https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109
Basically something like:
Measure = VAR __Date = MAX('Table'[Date]) VAR __Calendar = TOPN( 3, FILTER( ADDCOLUMNS(CALENDAR(__Date - 4,__Date-1),"__WeekDay",WEEKDAY(__Date,2)), [__WeekDay]<6 ) ) RETURN SUMX(FILTER('Table',[Date] IN __Calendar),[Value])Greg_Deckler
Thanks for such a quick response. Will you be able to explain me the logic?
I tried the following but may be I am using it wrong as it throws error:
The number of arguments is invalid. Function CONTAINSROW must have a value for each column in the table expression.Forecast 2 =VAR __Date = MAX('Date Core'[Date])VAR __Calendar =TOPN(3,FILTER(ADDCOLUMNS(CALENDAR(__Date - 4,__Date-1),"__WeekDay",WEEKDAY(__Date,2)),[__WeekDay]<6))RETURNSUMX(FILTER(' Retention Core Presenting Date',' Retention Core Presenting Date'[Presenting Date] IN __Calendar),'Date Core'[Accounts Presented])- Greg_Deckler
Anonymous My bad, I almost got it right without testing see below. Basically you create a quick calendar table with dates -4 and -1 from whatever the date is within context. You add a column to determine the weekday of each row of dates in the calendar table, you filterout weekends, grab the 3 latest dates and then you need the SELECTCOLUMNS to get it down to a single column (because we had to add one). Then you can use a FILTER with an IN to filter your table down to just those dates. That's the essence of it.
Measure = VAR __Date = MAX('Table'[Date]) VAR __Calendar = SELECTCOLUMNS( TOPN( 3, FILTER( ADDCOLUMNS(CALENDAR(__Date - 4,__Date-1),"__WeekDay",WEEKDAY(__Date,2)), [__WeekDay]<6 ) ), "Date",[Date] ) RETURN SUMX(FILTER('Table',[Date] IN __Calendar),[Value])
Hi Anonymous ,
I created some data:
Main Table:
Date:
Here are the steps you can follow：
1. Create calculated column.
weekday = WEEKDAY('MainTable'[Date],2)workday = IF( 'MainTable'[weekday] in {6,7},0,1)Index = RANKX(FILTER(MainTable,'MainTable'[workday]=1),[Date],,ASC,Dense)Sum_3day = CALCULATE(SUM('MainTable'[amount]),FILTER(ALL(MainTable),'MainTable'[Index]>=EARLIER(MainTable[Index])+2&&'MainTable'[Index]<=EARLIER(MainTable[Index])+4))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
