Forum Discussion
Project Month Number
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.
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?
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
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) )
