Forum Discussion

THEG72's avatar
THEG72
Helper V
9 years ago

Project Month Number

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.   ...
Need Help
Show and Tell
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

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's avatar
THEG72
Helper V
9 years ago

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's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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

     

     

    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      9 years ago

      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)
                       )
    • THEG72's avatar
      THEG72
      Helper V
      9 years ago

      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's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

        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.