3 Replies Latest reply on Dec 7, 2017 2:28 AM by Mike Gurajek

Monthly report - current month data

Hello everyone. I'm quite new to Tableau and I was asked to create a reporting tool for monthly reports.

I have a database which registers job requests - Start date, End Date, and some other info. What I need to do is to create a report counting how many jobs were active within the current month. So let's say we want a report for October: the report should show all the jobs that were opened and closed in October, all the jobs that were opened in months previous to October but were closed in October or stayed opened beyond October (hope it makes sense!).

I know in theory how it should work (check what current month is, check when the job was started and what the end date (or if it is null)) - but I fail miserably when I try to put it all together - my calculations grown arms and legs and in the end they do not work.

(sorry, I cannot publish a workbook as data is sensitive)

Any help in this matter would be greatly appreciated,

• 1. Re: Monthly report - current month data

Hi Mike,

Please provide dummy dataset. Without knowing the exact datastructure is hard to provide a proper solution.

Regards,

Norbert

• 2. Re: Monthly report - current month data

Try this calculation. It will return True for those active within the month.

//Started Current Month

DATETRUNC('month',[StartDate]) = DATETRUNC('month',TODAY())

//Ended Current Month

OR

DATETRUNC('month',[EndDate]) = DATETRUNC('month',TODAY())

//Started earlier than current month but not yet ended - assuming End Date is null for those not closed.

OR

(DATETRUNC('month',[StartDate]) < DATETRUNC('month',TODAY()) AND ISNULL([EndDate]))

1 of 1 people found this helpful
• 3. Re: Monthly report - current month data

Thanks Andrew - it worked like a charm!