Forum Discussion
Project Month Number
I can actually store the project start date and project end date in the job list table. So i have the date start and finish in my database already for each project..so i dont need to create a new table for this data.
So, if i have the Start and End date for each project already I just need a new parameter i guess called reporting period which can then be used to work out project month.....
It still needs to be worked out based on something.
So if your Project table has 1 row for a particular project. Which includes the ID, StartDate and EndDate.
Say this project started on the 1st of Jan 2015 and you want to know how many months have elapsed for that project as at the 1st of Jan 2016. You still need a Date table to fill in the gaps to give your measure what it needs to work with.
The calucated table I provided gives this. You don't need a table, and you could have the same code sitting in a calculated measure but I think you need the same approach. Unless you start hardcoding.
Hi Phill, thanks for taking time to post a reply on this forum.
Yes, I have a job and date dimension tables.
The header job contains the project information with start and end date, which will provide the calculation basis for total number (duration) of months for each job/project calculation.
So from this information I am trying to display:
1. The current project month number based on financial reporting period. For example, December 2016 which is project month 38.
I can obtain the reporting period by accesing the LASTDATE function on transaction table under normal conditions. Or i can use the last reconcilation date on the bank account however both are open to errors. Maybe use a parameter is easier?
2. The project duration for example using above dates is 140 (Oct 2013 to June 2025)
I would then want to be able to display
Current Project Month 38 / Project duration 140
I also run calculation for project month over duration for percentage to complete information.