Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Create a Date Dimension and connect to your faCt table then use this function
Proud to be a Super User!
Previous Business Day =
VAR lookatdate = VALUES(Dates[Date])
VAR previousbusinesday = IF(WEEKDAY(lookatdate,2) = 1, lookatdate - 3, lookatdate - 1)
return
previousbusinesday
@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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |