Multiple Counting with a date range criteria

[Dadoge]

Hello,

I'm dealing with a scenario where I have a table named "Table 1" with project ID, start date, and end date columns. 

What I aim to achieve is to count ongoing projects for each month within a list of dates between 2020 and 2023 (that doesnt exist). I want to create a line chart visual which display the number of ongoing project monthly for the past 3 years.

For example, if Project ID number 123456  has a start date of 01/01/2020 and an end date of 01/04/2020, I want to count it as an ongoing project in the line chart for the months 01/2020, 02/2020, 03/2020, and 04/2020 (So for each month there should be a count of 1 because ofthis project)

Thanks in advance,

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result in a Table format.  Share data in a format that can be pasted in an MS Excel file.

[Dadoge]

ID          Start Date     End Date 

1	01/01/2024	02/07/2024
2	20/08/2020	04/07/2023
3	30/10/2021	01/01/2022
4	23/12/2022	04/05/2023

As for the expected results I want to have a line chart that shows the number of ongoing project per month. Each project will be counted on going for each month between their start date and end date.

[Ashish_Mathur]

PBI file attached.

Hope this helps.

[v-tianyich-msft]

Hi @Dadoge ,

I just made a similar requirement, you can refer to this link:Calculate sum() according to a date range - Microsoft Fabric Community, if still in doubt please provide sample data to better solve your problem.

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Dadoge]

Hello,

thank you for your answer but it unfortunately doesnt work for me.

Here is the data set

IDStart DateEnd Date 

1	01/01/2024	02/07/2024
2	20/08/2020	04/07/2023
3	30/10/2021	01/01/2022
4	23/12/2022	04/05/2023

[v-tianyich-msft]

Hi @Dadoge ,

Depending on the requirement you may also have a date sheet, what I have done is to operate on the same date sheet and you can check the result as below:

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Dadoge]

Thanks for your quick answer !

What i meant with the expected results is that with your data set, there should be a spike for each month between  2020-08-20 and 2023-07-04.