Project Month Number

Question

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 · Answer

A measure like this might work :&nbsp;&nbsp;Current Project Month = DATEDIFF(
				MIN(Projects[Start Date]),
				NOW(),
				MONTH
			)This measure is based on a simple table as below called [Projects]. &nbsp;The measure will always show the current month number based on the startdate of the project.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

theg72 · Answer

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 · Answer

Oh ok, I think I know what you mean. &nbsp;The example I gave you is simple and assumes you are only looking at it from now.&nbsp;Will sort that for you in a tick&nbsp;&nbsp;

phil_seamark · Answer

Ok, this code assumes you have a date table called [Dates] and it has a column called [date].&nbsp;It will provide a table showing every day of a project for each project which you can build a measure off.&nbsp;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.&nbsp;Create a new table as :&nbsp;New Table = SELECTCOLUMNS(
                    FILTER(
	                   CROSSJOIN(Projects,Dates) ,
                        'Projects'[Start Date] &lt; 'Dates'[Date] &amp;&amp; 'Dates'[Date] &lt;= NOW()
                        ),
                       "Project Name",[Project Name] ,
                       "Date" ,'Dates'[Date],
                       "Project Month Number" , DATEDIFF('Projects'[Start Date],Dates[Date],MONTH)
                       )

theg72 · Answer

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.&nbsp;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.....&nbsp;&nbsp;

Forum Discussion

Project Month Number

7 Replies

Related content

Month name to Month Numbers

FORMAT() returns Month Number

Power BI: Month - Sort By Month Number

month name to number

Missing Project Name