Hi,
I am trying to calculate the number of selling days in a full month so that I can do a projection calculation. The calculation to calculate the number of selling days to date is correct (so far anyway).
my calculation is:
DATEDIFF("weekday", DATETRUNC('month',[End Date Parameter]), DATEADD('second',-1,DATEADD('month',1,DATETRUNC('month',[End Date Parameter]))))
- 2 * (DATEPART('week', DATEADD('second',-1,DATEADD('month',1,DATETRUNC('month',[End Date Parameter])))) - DATEPART('week', DATETRUNC('month',[End Date Parameter])))
+ (IF DATENAME('weekday',DATEADD('second',-1,DATEADD('month',1,DATETRUNC('month',[End Date Parameter])))) = 'Saturday' OR DATENAME('weekday',DATETRUNC('month',[End Date Parameter])) = 'Sunday' THEN 0 ELSE 1 END)
Alternatively is there a better way to calculate the number of working days between 2 dates? I need to calculate this for the month as well as the financial year. The Financial year start date is driven off a parameter as is the Report end date.
regards
Geneth,
I didn't look through your field carefully,
so apologies in advance if this is offbase.
Further apologies if you've already seen these links,:
Re: DateDiff for Working days only
http://www.clearlyandsimply.com/clearly_and_simply/2015/05/us-public-holidays-in-tableau.html
Similar to your approach, the above could be used to flag weekends and holidays
with a 0, the others with a 1, and then sum those between two dates.