Find all related projects for task based on date criteria

[IvanS]

Hi guys,

I am trying to figure out how can find and add all active projects in time of task creation based on project start date and project end date. I was trying to use CROSSJOIN and GENERATESERIES but unfortunately I have only managed to calculate number of active project in the time of task creation.

Active Projects = 
VAR _ActiveProjectCount = 
COUNTROWS(
    FILTER(
    DIM_Projects, 
    NOT ISBLANK(DIM_Projects[START_DATE]) &&
    'FACT_Tasks'[START_DATE] >= DIM_Projects[START_DATE] && 
    OR('FACT_Tasks'[START_DATE] < DIM_Projects[END_DATE], ISBLANK(DIM_Projects[END_DATE]))
))

RETURN
IF(ISBLANK(_ActiveProjectCount), 0, _ActiveProjectCount)

Please see example below:

I have 2 tables: FACT_Tasks and DIM_Projects. There is no relationship between these tables.

FACT_Tasks

TASK_ID	START_DATE
1	17.11.2022
2	15.12.2022
3	20.1.2023

DIM_Projects

PROJECT_NAME	START_DATE	END_DATE
ABC	20.11.2022	19.12.2022
DEF	14.12.2022
XYZ	15.1.2023

Now, I would need to list all projects in "Active projects" field in FACT_Tasks table separated by comma. It does not matter if it is calculated as column/measure or new calculated table.

TASK_ID	START_DATE	Active projects (desired field)
1	17.11.2022	(blank)
2	15.12.2022	ABC,DEF
3	20.1.2023	DEF,XYZ

Any help is much appreciated!

Thank you

IvanS

[Ashish_Mathur]

Hi,

Write this calculated column formula in the Task table

Column = CALCULATE(CONCATENATEX(VALUES(Projects[PROJECT_NAME]),Projects[PROJECT_NAME],","),FILTER(Projects,Projects[START_DATE]<=EARLIER(Tasks[START_DATE])&&Projects[End date]>=EARLIER(Tasks[START_DATE])))

Hope this helps.

[TomMartens]

Hey @IvanS ,

can you please explain why Task ID 1 has no active projects and why project XYZ is  not active in Task 2.
I assume Project ABC is not active in task 3, because the project closed before the task started.

Please list all relevant business rules.

Regards,

Tom

[IvanS]

Hi @TomMartens ,

I am looking for the nr. of active project in time of task creation.

Task 1 was created before all the projects. This will most likely not happen in real-life but wanted to highlight.

Task 2 was created before project XYZ, therefore this project was not active in time of Task 2 creation.

And regarding Task 3, your assumption is correct 🙂

[TomMartens]

Hey @IvanS ,

hmm, I'm still not sure if the business rules are fully described by your answer. For this reason I give it a try:

Projects are consider active for a task, when the

• task starts on or after the project start date
• but before the project end date.

Regarding the object to create CALCULATED COLUMN or MEASURE, is the expected result affected by user interaction with slicers.

Regards,
Tom

[TomMartens · Accepted solution]

Hey @IvanS ,

based on the business rule I described in my previous post, the DAX statement below creates a calculated column inside the table Fact_Tasks:

Active Projects = 
 var TaskStartDate = 'Fact_Tasks'[START_DATE]
 var activeProjects = 
    FILTER(
        'Dim_Project'
        , 'Dim_Project'[START_DATE] <= TaskStartDate && (ISBLANK('Dim_Project'[END_DATE] ) || 'Dim_Project'[END_DATE] >= TaskStartDate )
    )
 return
CONCATENATEX(
    activeProjects
    , 'Dim_Project'[PROJECT_NAME]
    , ","
    , 'Dim_Project'[PROJECT_NAME] , ASC
)

The next picture shows the result:

Hopefully, this provides what you are looking for.

Regards,
Tom

[IvanS]

This is just perfect! Thank you @TomMartens 

For solution provided by @Ashish_Mathur , this solution worked as well but Tom's solution is also sorting the projects 🙂  Thank you as well!