The following gives you weekend days between two days, I am sure you can retrofit it to figure out what you need. (If not, reply back here ....)
Reporting date can be Today and Transaction Date can be End of months which you already know how to find.
// Calculate Weekend days
INT((DATEDIFF('day',[Reporting Date],[Transaction Date])+ // Total Days
DATEPART('weekday',[Reporting Date])) /7 //How close to the next week is the reporting date (over/under compensate)
*2 //Times two because Sat/Sun
+ (if DATEPART('weekday',[Reporting Date]) = 1 then 1 else 0 end)// Add 1 because if reporting is a Sunday
- (if DATEPART('weekday',[Transaction Date]) = 7 then 1 else 0 end)// Subtract 1 if Transaction date is Sunday
If you have time could you please take the following quick survey to let us know about your Tableau Community Forums experience?
[Program Manager | Tableau Community Forums]
Thank you for your response Subodh.
I am not getting the correct answer. Today can fall on any day when I refresh my dashboard and so can the last day fall on any day too (regarding where the comment says add 1 or subtract 1 if it falls on Sunday).
I would appreciate more help. Thank you very much!
INT((DATEDIFF('day', [Today],[Last Day of Month])+
+ (if DATEPART('weekday',[Today]) = 1 then 1 else 0 end)
- (if DATEPART('weekday',[Last Day of Month]) = 7 then 1 else 0 end)
1 of 1 people found this helpful
I found an answer. The below formula did the trick. Please feel free to use whoever needs it
DATEDIFF("weekday", [Today], [Last Day of Month])
- 2 * (DATEPART('week', [Last Day of Month]) - DATEPART('week', TODAY()))
+ (IF DATENAME('weekday',[Last Day of Month]) = 'Saturday' OR DATENAME('weekday', TODAY()) = 'Sunday'
THEN 0 ELSE 1 END) -1
Is [Last Day of Month] here what you derived in your original post?
Yes! Its a part of it.
Last day of the month
DATEADD("weekday", -1, dateadd("month", 1, DATETRUNC("month",[Today])))
Question: Now, I need the first day of the month. How could I edit the current formula?
Any help appreciated.
(I need to calculate how WEEK DAYS have ELAPSED from first day of the month to today. Basically reverse of everything I did till now)
I was just writing the first day for something.
Hi Subodh. Weird that Madedate was working yesterday and today it is giving me "Unknown Function" error.
I feel like I am missing something here. Any idea?
Makedate, note Madedate. I think that might be the issue, hope not. Or are you using it on a live connection? It might only work on an extract. !
I meant MakeDate. I am using a live connection. I do create extracts when I need to share a report. But I need a solution that will work always.
MAKEDATE() won't work on live connections. You will have to use a database/source-specific function. What is the source of your live connection?