The DATE function will do that for you.
Getting the last day of the month ... maybe there is some function I'm missing, but I'm not sure how to tell the calc to be the last day of a given month. If I had to do this with my limited knowledge, I would add one to the month, set the day value to 1 (so if you have 5 in your field, create 6/1) and then do a DATEADD of -1 to go back one day. (You'll have to check if your month is 12. Set it to 1 instead of adding 1, add 1 to the year, so that you end up with 1/1/201x for next year, then do the DATEADD of -1 to go back a day.)
Of course, instead of doing the dateadd, you could hard-code all the last-days of each month and cram that value in there.
1 of 1 people found this helpful
A better way to get the date from month and year is:
DATEADD('month',[Month]-1,DATEADD('year', [Year]-2000, #1/1/2000#))
To get the last day of the current month, you can add a day, truncate to month, and subtract a day:
Thanks and based on your recommendation:
last day of the month
DATEADD('month',[Month]-1,DATEADD('year', [Year]-2000, #1/31/2000#))
One more quesiton for you:
Do you know a best way to calculate Net Working Days between two dates in Tableau?
Seached and got no correct answer so far. It seems that you are good at with formula.
I have checked this calculation before.
April 2014 should have 22 business days and it bring up 21 business day.
Datediff('week',[First day of the month ],[Last day of the month])*5
Min(datepart('weekday',[Last day of the month]),5)
Min(datepart('weekday',[First day of the month ]),5)
seems does not get correct net working days in July regardless of any holiday.
does not work on some of the months
for example the fomular shows 21 in Feb 2014 it is actual 20 days excluding Weekends.