Segment Project Dimension by gaps in time line

[JoachimSA]

Dear all,

I am having trouble creating the column "SOP Cluster" in DAX:

So basically I want to segment each project into vrious clusters defined by GAPs in the SOP timeline. I would like to define the length of this GAP in month later. For now just at least one calendar year without SOPs.

The SOP Column is linked to a proper calendar table.

THe real Project table is huge and consists of many thousand Projects consisting of many more Vehicles.

I would like to solve this in DAX because I would like to be able to later extend the logic for segmentation.

For Example by defining a GAP as a year with "very low" volume ( based on some [volume] measures from the fact table.

Thanks an best regards,

Joachim

[v-henryk-mstf · Accepted solution]

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.