Forum Discussion

Dadoge's avatar
Dadoge
Frequent Visitor
2 years ago

Multiple Counting with a date range criteria

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,

 

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Dadoge
      Frequent Visitor
      2 years ago

      Hello,

       

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

       

      Here is the data set

       

      IDStart DateEnd Date 

      101/01/202402/07/2024
      220/08/202004/07/2023
      330/10/202101/01/2022
      423/12/202204/05/2023
      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.

         

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    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's avatar
      Dadoge
      Frequent Visitor
      2 years ago

      ID          Start Date     End Date 

      101/01/202402/07/2024
      220/08/202004/07/2023
      330/10/202101/01/2022
      423/12/202204/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.