cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## Calculating Project Month

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

1 ACCEPTED SOLUTION
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
2 REPLIES 2
Super User

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper V

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors