Number of active projects per month knowing start date and end date

[tony_BI34fr]

Hi Power BI Community,

I am trying to create a measure to know the number of active projects per project manager per month.

My source data looks like this:

projects	Project Manager	start date	end date
projet 1	PM1	March-22	null
projet 2	PM2	April-22	May-22
projet 3	PM1	February-22	May-22
projet 4	PM1	March-22	July-22
projet 5	PM3	March-22	August-22
projet 6	PM2	April-22	null
projet 7	PM4	February-22	null
projet 8	PM4	March-22	June-22

I would like my result in Power BI to look like this:

	January-22	February-22	March-22	April-22	May-22	June-22	July-22	August-22	September-22	October-22	November-22	December-22
PM1	0	1	3	3	2	2	1	1	1	1	1	1
PM2	0	0	0	2	1	1	1	1	1	1	1	1
PM3	0	0	1	1	1	1	1	0	0	0	0	0
PM4	0	1	2	2	2	1	1	1	1	1	1	1

I feel like it's not too difficult but somehow I can't wrap my head around this!

Let me know if you need more information.

Thank you

[tony_BI34fr]

OK, it gets interesting.

As stated above, it works nicely in Power BI Desktop. When I publish it to the service and refresh the dataset, it breaks the count! 

[tony_BI34fr]

Final update: 

For some reason, updating the dataset in Power BI desktop was working smoothly, however, as stated above, updating the dataset from the Power BI Service was no working properly. The cause was that the MySQL database (on-prem with a data gateway) startDate and endDate columns were sometimes null or 0000-00-00. The later was not working with the Power BI Service.  

I then changed my SQL query to null those messy dates.

[tony_BI34fr]

Thank you for your replies. I currently don't have the time to test all this but I'll get back to you.

Thanks again.

edit: I accepted Greg's solution as it is more adapted to my need.

The first link you provided works beautifuly !

Thank you very much

Based on the source data I provided above, the working measure is:

Projects Open =
VAR tmpProjects = ADDCOLUMNS('data',"Effective Date",IF(ISBLANK([end date]),TODAY(),[end date]))
VAR tmpTable =
SELECTCOLUMNS(
FILTER(
GENERATE(
tmpProjects,
'date'
),
[date] >= [start date] &&
[date] <= [Effective Date]
),
"ID",[projects],
"Date",[date]
)
VAR tmpTable1 = GROUPBY(tmpTable,[ID],"Count",COUNTX(CURRENTGROUP(),[date]))
RETURN COUNTROWS(tmpTable1)

This is assuming there is a non joined calendar table named 'date' containing the column [date] (created using Power Query for this test).

[v-yangliu-msft]

Hi  @tony_BI34fr ,

Here are the steps you can follow：

1. Power Query – Select [start date] , [end date] – Transform – Unpivot Columns.

Result:

2. Rows – [Project Manager] , Columns – [Value] , Value –Set [Projects]  to Count.

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Greg_Deckler · Accepted solution]

@tony_BI34fr 
Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365