Project Month Number

[THEG72]

Just trying to work out the best way to add a project month number for reporting.

Currently, i have 4 projects in my accounting file.

They all have different project start and end dates.

For example one project is 140 months duration, commencing on October 2013 (project Month= zero) and finishing on June 2025 (Project Month = 140), another starts in June 2014 and finishes June 2024 etc.

What is the best method to create this information so i can show the current project month number. For example based on a start date of Oct 2013 and end date of June 2025 the current project month number would be 40 which represents February 2017.

Should i create a table of data with start and end project months or a new parameter key for each project. And then how do I link this to the DATEDIM table?

I would like the method to work so when i choose the current reporting month (may need this as a new parameter) it will display the current project month numer. Choosing the current month based on todays date wont work as normally we are reporting historical results in the current period. For example, December 2016 figures are finalised now and we are reporting December figures (month 38) in February 2017.

Thanks again for any advice.

[Phil_Seamark]

A measure like this might work :

Current Project Month = DATEDIFF(
				MIN(Projects[Start Date]),
				NOW(),
				MONTH
			)

This measure is based on a simple table as below called [Projects].  The measure will always show the current month number based on the startdate of the project.

[THEG72]

How does the measure know what the current reporting month is? For example, I am reporting on December 2016 figures in the actual month of February 2017?

Do i need a parameter called Current Reporting period so the system can obtained the current project month?

[Phil_Seamark]

Oh ok, I think I know what you mean.  The example I gave you is simple and assumes you are only looking at it from now.

Will sort that for you in a tick

[THEG72]

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.....

[Phil_Seamark]

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.

[THEG72]

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.

[Phil_Seamark]

Ok, this code assumes you have a date table called [Dates] and it has a column called [date].

It will provide a table showing every day of a project for each project which you can build a measure off.

I can reduce the size of the table for you to months if you like, only it makes the DAX look busier, so if you like this approach, I can then provide a more suitable peice of code.

Create a new table as :

New Table = SELECTCOLUMNS(
                    FILTER(
	                   CROSSJOIN(Projects,Dates) ,
                        'Projects'[Start Date] < 'Dates'[Date] && 'Dates'[Date] <= NOW()
                        ),
                       "Project Name",[Project Name] ,
                       "Date" ,'Dates'[Date],
                       "Project Month Number" , DATEDIFF('Projects'[Start Date],Dates[Date],MONTH)
                       )