Calculating Project Month

[THEG72]

I have this table showing jobs with start and end dates...Project A (D1) and Project B (R1)

JOB TABLE SHOWING PROJECT DATES AND DURATION CALC COLUMN

I have a guage to show monthly progress of jobs comparing start and end date with todays month

Gauge showing Completion %

Project D1/A = 43 months divided by 140 months = 31% which is correct 

Project R1/B = 36 months divided by 73 monhts = should show 49% but instead is shown 59% which is 36 divided by 140 (Project A/d1's duration is being used instead of 73 months duration for project R1/B)

Here are the DAX entries i made to get the results

Project durations are created as a caculated column in the JOBS Table.

Project Completion R1 % = DIVIDE([Current Project Month],[Project Duration R1])  (this is obviously not choosing correct duration from table..so do i need to say if Manager = P2 then use duration?)

Project Completion D1 % = DIVIDE([Current Project Month],[Project Duration D1])

Then I have a measure to work out current month

 Current Project Month = DATEDIFF(MIN(JOBS[Project Start Date]),[Last Transaction Date],MONTH)

Any ideas appreciated..cheers

[Greg_Deckler · Accepted solution]

Where is [Last Transaction Date] coming from?

For [Project Duration R1] you should be able to create a measure like:

[Project Duration R1] = CALCULATE(MAX([Project Duration]),FILTER('JOB TABLE',[Job Manager] = "P2"))

[THEG72]

Hey @Greg_Deckler

Thanks for the reply,

My Last transaction date is derived from the journal table which holds all transactions

Last Transaction Date = CALCULATE(LASTDATE(JOURNALRECORDS[Date]),ALL(JOURNALRECORDS))

I have worked a solution but just checking my method is the best approach.

The Project duration is a calculated field in the Job Table...so that works fine...

Its the Project % completion in my guage which i want to know if a measure can be used...

The project completion % only works if I choose in the visual to select Manager = p2

i tried the following, based on your answer and get an error 

Project Completion R1% = CALCULATE(MAX([Project Duration R1]),FILTER(JOBS[Manager]= "P2"))  but i get an error stating too few arguments were passed to the FILTER function, the min arguement count for the function is 2.

This my formula which works to calculate Duration for each project

Project Duration R1 = CALCULATE(sum(JOBS[Project Duration (Months)]),JOBS[Manager] = "P2")