Histogram showing cummulated delays at each week

[rme]

Hello

I'm a complete newbie looking for some help.

I'm trying to create a histogram that shows the no. of projects that are delayed. If a project is delayed by multiple weeks, it shall be counted/shown in each week of delay and not just a sum.

In below example I try to illustrate what I want to achieve. You will find the matching colors in the table and graph to show the effect that I want to achieve. (the yellow row with no delay shall not appear in the graph)

,      

I hope someone can help me with a DAX measure. It would be highly appreciated. Thanks in advance for your efforts!

[rme]

Dear Yadong Fang

Thank you very much for your support and the good solution.
Best regards
rme

[v-yadongf-msft]

Hi @rme ,

This is my test table:

Create a calendar table:

CalendarTable = ADDCOLUMNS(CALENDAR(DATE(2022,1,1),DATE(2023,12,31)),"Year_Week",YEAR([Date]) &"-"& WEEKNUM([Date],2))

Create a measure:

Measure = 
var max_date = MAX('CalendarTable'[Date])
VAR delay_project = FILTER('Table','Table'[Planned] <= max_date && 'Table'[Forecasted]> max_date)
RETURN
COUNTROWS(delay_project)

The result you want:

Best regards,

Yadong Fang

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

[rme]

Dear Yadong Fang

Thank you very much for the fast response. Great!

I must admit, it really looks complicate for beginner like me. In the example I provided only 6 projects. In reality I need to track thousands of projects/orders. The list of projects ist dynamically growing. Therefore, I can't "hard-type" the project names in a DAX measure. Can you think of a solution for this?

I might be wrong, but could it become a problem if I have to create 3 new columns in the calendar table for each project, having severaI thousand projects?

Hope you can assist me further on my journey. 

Best regards

rme

[v-yadongf-msft · Accepted solution]

Hi @rme 

I've updated my original reply. I think this method is suitable for you. Please refer the attached pbix file.

Best regards,

Yadong Fang