A countif formula for project count by month

[Anonymous]

Hi, 

I am new to powerbi and I am trying to create a dashboard to report on active projects. I want to be able to display how many active projects there are for each project manager each month. I have the project manager's name, the projects name, and forcasted closed date.  I am thinking about using it in a line graph.  For example, if Jenny has 5 active projects in January and one of then has a forecasted close date of 2/4/23 then the count for February would be 4 active projects.  I cannot figure out the countif formula to use in the line graph. 

[FreemanZ]

hi @Anonymous 

try to 

1) creat an unrelated date table with at least date and month columns

2) write a measure like:

measure =

VAR _mindate = MIN(DateTable[Date])

VAR _maxdate = MAX(DateTable[Date])

RETURN 

COUNTROWS(

    FILTER(

       ALL(TableName), 

       TableName[StartDate]<=_maxdate&&TableName[CloseDate]>=_mindate

    )

)

3) plot a line chart visual with datetable[month] column to x axis and the created measure to value, project manager column to the legend.