I am trying to show trending and comparison of funding for the past 3 months. So to accomplish this I have to show the Business day number and the loan amount then the line graph for each month. What I have managed to do is to put the calendar date but the client whats the business Day number. An example is for the month of June instead of showing the 30 days I expect 22 days.
How can I accomplish this
Attached is the workbook with what I have done.
Thank you in advance.
Hey Tapiwa,
The best way I can think of doing this is to utilize the calculation for number of business days. The use is going to calculate the number of business days from the start of the month to the date field in question. So, your example of Jun 3 would calculate the number of business days between Jun 1 and Jun 3... 2. Which is the desired result. This calculation is:
DATEDIFF("weekday", DATETRUNC('month', [Order Date]), [Order Date])
- 2 * (DATEPART('week', [Order Date]) -DATEPART('week', DATETRUNC('month', [Order Date])))
+ (IF DATENAME('weekday',[Order Date]) = 'Saturday' OR DATENAME('weekday',DATETRUNC('month', [Order Date])) = 'Sunday'
THEN 0 ELSE 1 END)
Note: this is from the attached workbook using Superstore, simply replace the [Order Date] with whichever date field you want to convert.
