Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jp2020
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
amitchandak
Super User
Super User

@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"
)

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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"
)

That has worked perfectly. Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.