Count of companies with projects within each month

[iruserwin]

Hi Helpful powerbi folks

I am trying to count the number of entities each month that have an action within the start date and end date of the action.

Entity 1

Action 1 Start date May 4 End date June 31

Action 2 Start date May 6 End date July 15

Action 3 Start Date July 2 End date July 27

Entity 1 should score 1 for May, June and July.  When my report gets to August it should score zero.

Entity 2

Action Bob start date May 13 End Date June 2

Action Fred start date May 17 End Date May 18

Action Jim start date June 2 End Date August 27

Entity 2 should score 1 for May, June, July and August

The data i need then is

May 2

June 2

July 2

Aug 1

With that i can use a bar chart visualization to show how many practices have actions in May, June etc.

The number of actions is irrelevant, simply that an action is happening in the particular month.

How do I find the earliest Start Date, the latest End Date, and make a table for the 1st of each month starting with Start Date and ending with End date.

Using my simplistic data above earliest start date = May 4, so my "calendar table" Starts May 1 and my latest end date = Aug 27 so my "calendar table" ends Aug 1.

Once i have a list of all the months how do i evaluate if the month on the calender table is between or equal to the start date end date of the action?

Then when there is more than one action for an entity only count the entity as 1 (count distinct?)

I want to get to a visualization like this that i made in excel.

thanks to the smart folks that may be able to assist!

[v-linyulu-msft · Accepted solution]

Hi,@iruserwin 

As far as I know, there are only 30 days in June in 2024, and there is an error in the data you gave and what I learned, so I will use June 30 in the data below.

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Regarding your need to create a table that includes all start and end dates and ends with the 1st of the start month, I created the following calculation table:

dateTable = 
VAR mindate=MINX(UNION(SELECTCOLUMNS('Entity 1',"1",'Entity 1'[Start date]),SELECTCOLUMNS('Entity 2',"1",'Entity 2'[start date])),[1])
VAR maxdate=MAXX(UNION(SELECTCOLUMNS('Entity 1',"2",'Entity 1'[End date]),SELECTCOLUMNS('Entity 2',"2",'Entity 2'[End Date])),[2])
RETURN
 CALENDAR (DATE (2024, MONTH(mindate), 1), DATE (2024, MONTH(maxdate), 1))

3. Regarding your first demand statistic how many operations per month:

I created the following measure and calculation table：

Measure = COUNTROWS(FILTER(SUMMARIZE(ADDCOLUMNS(CALENDAR(MIN('Entity 1'[Start date]),MAX('Entity 1'[End date])),"Month",FORMAT([Date],"MMMM")),[Month]),[Month]=SELECTEDVALUE(result[Month])))+COUNTROWS(FILTER(SUMMARIZE(ADDCOLUMNS(CALENDAR(MIN('Entity 2'[Start date]),MAX('Entity 2'[End date])),"Month",FORMAT([Date],"MMMM")),[Month]),[Month]=SELECTEDVALUE(result[Month])))

result = SUMMARIZE('dateTable',dateTable[Date].[Month])

The results of the visualization are as follows:

4.Here's my final result, which I hope meets your requirements.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Leroy Lu

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