Segment Project Dimension by gaps in time line

Hi JoachimSA ,

To create the "SOP Cluster" column in DAX, you can use the following formula:

• First, create a new calculated column in your Project table called "SOP Date Diff", which calculates the difference between the latest SOP date for each project and the earliest SOP date for each project, in months. You can use the DATEDIFF function to do this:

SOP Date Diff = DATEDIFF(Project[Earliest SOP Date], Project[Latest SOP Date], MONTH)

• Next, create a new calculated column called "SOP Cluster", which assigns each project to a cluster based on the value in the "SOP Date Diff" column. You can use an IF statement to do this:

SOP Cluster =
IF(Project[SOP Date Diff] >= 12, "Cluster 1",
IF(Project[SOP Date Diff] >= 6, "Cluster 2",
IF(Project[SOP Date Diff] >= 3, "Cluster 3",
IF(Project[SOP Date Diff] >= 1, "Cluster 4",
"Cluster 5"))))

This formula assigns projects with a SOP date difference of at least 12 months to Cluster 1, projects with a SOP date difference of at least 6 months but less than 12 months to Cluster 2, and so on. You can adjust the criteria and number of clusters to fit your specific needs.

You can then use the SOP Cluster column as a visual filter or as a slicer in your Power BI report to segment the projects by SOP cluster.

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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