HELP: Opened and Closed Projects

[SDigital]

Hi

I am trying to produce a graph that shows the amount of projects opened and closed per month.

My dataset consists of two tables: Project Definition and a Calendar table. 

The Project Definition table contains "date created" "status last changed date" and "status" . Project Definition and Calendar are joined on "date created".

I am using the "status last changed date" as the closed date if the status = closed

I have two measures counting closed and open projects, but the numbers don't add up on the graph. 

I suspect it is due to the nature of the relationship between the project and calendar table.

Any help is appreciated

Thanks in advance

[v-yuta-msft]

Hi SDigital,

What's the table structure and relationship between your tables currently? Could you share the dax formula you are using?

Regards,

Jimmy Tao

[SDigital]

Hi Jimmy

I have a one to many relationship between Calendar/Date and Project Definition/Created Date

I also have another inactive relationship between Calendar/Date and Project Definiton/Status last changed date

The measures i am using to count the opened and closed projects are:

Projects Opened = COUNTROWS('Project Definition')

Closed Projects = CALCULATE(COUNTROWS('Project Definition'),('Project Definition'[Status] = "ZCLS"),USERELATIONSHIP('Project Definition'[Status last changed date],'Calendar'[Date]))

Thanks