Projects per month vs hours to complete

[chitti5]

We have 123 Projects out of which 13 are completed and 110 are still active. each project takes 60 hours to complete from the date it got started. We take 6 months to complete each project. How do I show the data in the form of a stacked column chart. Is there any DAX to show the data from Jan to Dec of 2024 per month how many projects are we currently working with 60 hours to complete each project in next 6 months. (FYI - This is a sample data)

https://docs.google.com/spreadsheets/d/1XPCvuTOzT0OjROe3HP_2uZpG7JFjmF7IIqdWLoml7pY/edit?usp=sharing

Thanks!

[Ashish_Mathur]

Hi,

In another tab of that file, show the expected result with your formulas.  I will try to convert those Excel formulas into DAX formulas.

[chitti5]

@Ashish_Mathur : Hello Sir,

The best I can explain is through the below table.

Month	Project started Count	active projects per month	Count * Hours spent ( usually it takes 6 months for each project to get completed and we allocate 60 hours for each project
Jan	3	3	3 * 60/6
Feb	2	2+3 = 5	5 * 60\6
Mar	4	4+5 = 9	9* 60/6
April	1	1+9 = 10	10* 60/6

If any project is completed before it's allocated time, it will be removed. For example in March we have 9 projects and 1 project is completed then instead of 9 projects we have only 8 projects moving forward to April.

Thank you.

[Ashish_Mathur]

Hi,

Of the 5 projects that were active till Feb end, if one completed on March 18, 2024, then will the active project count in March be 5+4-1=8?

[chitti5]

Yes Sir, you are correct.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Anonymous]

Hi @chitti5 ,
I opened the link where you provided the sample data, but it says it can't be opened, you can follow the link below to provide oh that sample data
How to Get Your Question Answered Quickly - Microsoft Fabric Community
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best regards,
Albert He