Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
@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"
)
@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!
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |