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