calculations excluding weekends

[eshanpacheriwal]

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? 

[v-yangliu-msft · Accepted solution]

Hi  @eshanpacheriwal ,

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

[Greg_Deckler]

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

[eshanpacheriwal]

@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

)

)

RETURN

SUMX(FILTER(' Retention Core Presenting Date',' Retention Core Presenting Date'[Presenting Date] IN __Calendar),'Date Core'[Accounts Presented])

[Greg_Deckler]

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

[eshanpacheriwal]

@Greg_Deckler 
I think I might be understanding the logic wrong. 
It is giving me the count of all the accounts till date instead of just the last three now. 

Forecast 2 =

VAR __Date = MAX('Date Core'[Date])

VAR __Calendar =

SELECTCOLUMNS(

TOPN(

3,

FILTER(

ADDCOLUMNS(CALENDAR(__Date - 4,__Date-1),"__WeekDay",WEEKDAY(__Date,2)),

[__WeekDay]<6

)

),"Date",__Date

)

RETURN

SUMX(FILTER('Date Core',__Date IN __Calendar),'Date Core'[Accounts Presented])

[Greg_Deckler]

@eshanpacheriwal May have to add the optional parameter to TOPN to change the sort direction.