Filtering to the Previous Business Day

[jp2020]

I have a daily data set in which I need to filter the data for the previos working day for Tuesday to Friday but on Monday I need it to filter to Friday, Sat and Sunday. Im not concerned about holidays for now.

So,

Today     Required

Mon       Fri, Sat, Sun

Tue         Mon

Wed       Tues

Thur       Wed

Fri          Thurs

[VijayP]

@jp2020 

Create a Date Dimension and connect to your faCt table then use this function

Amount1 =

IF(FORMAT(SELECTEDVALUE('Date'[Date]),"DDD")="Mon",

CALCULATE(SUM('Table'[Amount]),DATEADD('Date'[Date],-1,DAY))+

CALCULATE(SUM('Table'[Amount]),DATEADD('Date'[Date],-2,DAY))+

CALCULATE(SUM('Table'[Amount]),DATEADD('Date'[Date],-3,DAY)),

CALCULATE(SUM('Table'[Amount]),DATEADD('Date'[Date],-1,DAY)))
attached PBIX file for clarity https://drive.google.com/file/d/16G-YYIBRgbIHi4xCY1Ex1Xxgo9HhdTP7/view?usp=sharing

[stevie_westside]

Previous Business Day =

VAR lookatdate = VALUES(Dates[Date])
VAR previousbusinesday = IF(WEEKDAY(lookatdate,2) = 1, lookatdate - 3, lookatdate - 1)

return
previousbusinesday

[jp2020]

@VijayP Thank you for the prompt response. I maybe should have been clearer on the question. I already have a date dimension table set up and linked to my other tables.

I want to add a new column to the date dimension table which would tell me the last business day based on my logic on my original post. I can then use this coulmn to apply filters to the a number of visulations I have.

So for example today, Monday 21 Sep I would like to see the below in the date dimension table.

Date                        BusinessDay

Thu 17th Sep          NA

Fri 18th Sep            PreviousWorkingDay

Sat 18th Sep           PreviousWorkingDay

Sun 19th Sep          PreviousWorkingDay

Mon 20th Sep        Today

Tue 21st Sep          NA

.....

Then tomorrow, Tuesday 22 Sept I would expect to see:

Date                        BusinessDay

Thu 17th Sep         NA

Fri 18th Sep           NA

Sat 18th Sep          NA

Sun 19th Sep         NA

Mon 20th Sep       PreviousWorkingDay

Tue 21st Sep         Today

Wed 22nd Sep      NA

....

And on Wed 23 Sep I would expect to see:

Thu 17th Sep         NA

Fri 18th Sep           NA

Sat 18th Sep          NA

Sun 19th Sep         NA

Mon 20th Sep       NA

Tue 21st Sep         PreviousWorkingDay

Wed 22nd Sep      Today

Thu 23rd Sep        NA

....

And so on

[v-deddai1-msft · Accepted solution]

Hi @jp2020 ，

I‘m confused about your expected output. Would you please explain more about it (21st Sep  was  Monday , why it is  Tuesday in your expected output)? And if you want to get Previous Business Day

First create a weekday column:

WEEKDAY = WEEKDAY('Table'[Date],2)

Then you can use it to get the Previous Business Day:

Previous Work Day = IF('Table'[WEEKDAY]>1&&'Table'[WEEKDAY]<=5,'Table'[Date]-1,CALCULATE(MAX('Table'[Date]),FILTER('Table','Table'[Date]<EARLIER('Table'[Date])&&'Table'[WEEKDAY] = 5))) 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai