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

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