cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Adding Previous Business Day Column to Date Dimension Table

I am looking to add a new column to my date dimension table to tell me the previous business day. I will then use this to add filters to visualisations I have. I am new to Power BI so I dont know where to start on this and any help will be greatly appreciated.

 

The nuance I have is for Monday I would need to count Fri, Sat and Sun and a previuos businesss day. So for example:

 

Today, Monday 21 Sep I would like to see in the date dimension table as:

 

Date                      PreviousBD

Thur 17 Sep          NA

Fri 18 Sep             PreviousBD
Sat 19 Sep            PreviousBD 

Sun 20 Sep           PreviousBD

Mon 21 Sep         Today

Tue 22 Sep           NA

 

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

 

Date                      PreviousBD

Thur 17 Sep          NA

Fri 18 Sep             NA
Sat 19 Sep            NA

Sun 20 Sep           NA

Mon 21 Sep         PreviousBD

Tue 22 Sep           Today

Wed 23 Sep         NA

.....

 

On Wed 23 Sep I would expect to see:

 

Date                      PreviousBD

Thur 17 Sep          NA

Fri 18 Sep             NA
Sat 19 Sep            NA

Sun 20 Sep           NA

Mon 21 Sep         NA

Tue 22 Sep          PreviousBD

Wed 23 Sep        Today

Thur 24 Sep         NA

 

And so on.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Adding Previous Business Day Column to Date Dimension Table

@jp2020 , Add a new column like

Switch(
True() ,
[Date]=today() , "Today",
weekday(today(),2) =1 && [Date]>=today()-4 && [Date]<today() , "PreviousBD" ,
weekday(today(),2) >1 && [Date] = today()-1 , "PreviousBD" ,
"NA"
)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
Highlighted
Super User IV
Super User IV

Re: Adding Previous Business Day Column to Date Dimension Table

@jp2020 , Add a new column like

Switch(
True() ,
[Date]=today() , "Today",
weekday(today(),2) =1 && [Date]>=today()-4 && [Date]<today() , "PreviousBD" ,
weekday(today(),2) >1 && [Date] = today()-1 , "PreviousBD" ,
"NA"
)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Helper I
Helper I

Re: Adding Previous Business Day Column to Date Dimension Table

That has worked perfectly. Thank you!

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors