Computing how many projects are open in any given month (with start and end dates)

[MariDR]

Hello! I am trying to compute how many projects are open in any given month based on start and end dates. I have a dynamic date table, and a main table with project ID (distinct), project owner (one project owner may have multiple projects), start date and end date. 

Project Owner	Project ID	Start Date	End Date
John A	0123	5/1/2024	8/31/2024
John A	1234	07/1/2024	10/1/2024
Mel A	2345	4/1/2024	7/15/2024
Mel B	3456	6/1/2024	9/1/2024

How do I go about with using a measure so that end product looks like the below table. Thanks in advance!

Project Owner	APR 2024	MAY 2024	JUN 2024	JUL 2024	AUG 2024	SEP 2024	OCT 2024
John A		1	1	2	2	1	1
Mel B	1	1	2	2	1	1

[amitchandak · Accepted solution]

@MariDR , Please follow the approach of give HR blog and file with a date table

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar problem in the attached PBI file.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

I have solved a similar problem in the attached PBI file.

Hope this helps.

[Anonymous]

Hi all,thanks for the quick reply, I'll add more.

Hi @MariDR ,

The Table data is shown below:

Please follow these steps:

1.Use the following DAX expression to create columns in 'Table'

Column = 
VAR _start = YEAR([Start Date]) * 100 + MONTH([Start Date])
VAR _end = YEAR([Start Date]) * 100 + MONTH([End Date])
VAR _table = GENERATESERIES(_start,_end,1)
VAR _table2 = CONCATENATEX(_table,[Value])
RETURN _table2

Column 2 = CALCULATE(CONCATENATEX('Table',[Column]),ALLEXCEPT('Table','Table'[Project Owner]))

2.Use the following DAX expression to create a table named 'Calendar'

Calendar = 
ADDCOLUMNS(CALENDAR(DATE(2023,1,1),DATE(2024,12,31)),
"Month",YEAR([Date]) & SWITCH(TRUE(),
                              MONTH([Date]) = 1, "Jan",
                              MONTH([Date]) = 2, "Feb",
                              MONTH([Date]) = 3, "Mar",
                              MONTH([Date]) = 4, "Apr",
                              MONTH([Date]) = 5, "May",
                              MONTH([Date]) = 6, "Jun",
                              MONTH([Date]) = 7, "Jul",
                              MONTH([Date]) = 8, "Aug",
                              MONTH([Date]) = 9, "Sep",
                              MONTH([Date]) = 10, "Oct",
                              MONTH([Date]) = 11, "Nov",
                              MONTH([Date]) = 12, "Dec"
                            ),
"MonthNumber",YEAR([Date]) *100 + MONTH([Date])
)

3.Use the following DAX expression to create a measure

Measure = 
VAR _MothNumber = SELECTEDVALUE(Calendar[MonthNumber])
VAR _len = LEN(MAX('Table'[Column 2]))
VAR _table = ADDCOLUMNS(GENERATESERIES(1,_len,6),"Month",MID(MAX('Table'[Column 2]),[Value],6))
RETURN COUNTROWS(FILTER(_table,[Month] = _MothNumber & ""))

4.Final output

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

[BITomS]

Hi @MariDR,

I think article tutorial help: https://community.fabric.microsoft.com/t5/Desktop/Tutorial-Fill-All-Dates-Between-Start-Date-and-End...

Once you extrapolate the dates between start and end into a single column, you can add month and year columns into your table and create a measure to identify distinct month/year combinations each project exists in.

Hope this helps!

[JinDR]

Thank you for this link. I was able to use the M method successfully and then connected it with a date table. I was wondering though if would it be possible to do the same list by month instead of by day? TIA.

[BITomS · Accepted solution]

This sounds like it might help: https://community.fabric.microsoft.com/t5/Power-Query/Date-difference-in-Months-Power-Query/td-p/199...

[amitchandak · Accepted solution]

@MariDR , Please follow the approach of give HR blog and file with a date table

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU