What are active tasks in a period

[Lucian]

Hello,

I have a simple problem but I cannot get to the desired result.

The simplified model consists in a Date table and a Task table:

Task	Start	End
A	01.03.2023	15.03.2023
B	20.03.2023	02.04.2023
C	25.03.2023	25.06.2023
D	01.04.2023	15.04.2023
E	12.04.2023	19.04.2023

I need a report having a Gannt visual to display the tasks that fall into a specific period:

Basically I need to see the tasks that will "intersect" the selected period created by the Year/Month/Week slicers. Because will be tasks that will start or end in the selected period the Date table could not be related (I guess) to the Tasks table.

I have tried the approach in the other thread: Filter activities in a period but the formula does not return proper values.

filtermeasure = 
var _start=MINX(ALLSELECTED('Date'),[Date])
var _end= MAXX(ALLSELECTED('Date'),[Date])
return IF(MIN(Tasks[Start])>=_start && MIN(Tasks[End]) <=_end,1,0)

For example selecting the month APR and week W14 resulting the period of 01.04.2023 - 02.04.2023 it should display tasks B, C  and D.

For APR / W17 period is 17.04.2023 - 23.04.2023 the displayed tasks should be C, D and E.

And when selecting only the month without any week the period would be 01.04.2023 - 30.04.2023 wich should display the B, C, D and E tasks.

For convenience, I have attached the PBIX file: Filter-Gantt.pbix

Kind Regards,

Lucian

[Lucian · Accepted solution]

Hi @amitchandak ,

Thank you for your suggestion. I have tried to replicate your measure for "Current Employees" as follows:

Current v1 = 
CALCULATE (
    COUNTX (
        FILTER (
            Tasks,
            Tasks[Start] <= MAX ( 'Date'[Date] )
                && (
                    ISBLANK ( Tasks[End] )
                        || Tasks[End] > MAX ( 'Date'[Date] )
                )
        ),
        ( Tasks[Task] )
    )
)

Still the formula does not return expected values/tasks. However, reviewing my post, and using your base idea, I have tried to play more with the "intersect", so I came up with this formula that it seems to cover all the cases:

Current v3 = 
//Set Watch Period
VAR PeriodMin=MIN('Date'[Date])
VAR PeriodMax=MAX('Date'[Date])
VAR tblPeriod=GENERATESERIES(PeriodMin,PeriodMax)

//Set Task Window
VAR TaskMin=COALESCE(MIN(Tasks[Start]),PeriodMin)
VAR TaskMax=COALESCE(MAX(Tasks[End]),PeriodMax)
VAR tblTask=GENERATESERIES(TaskMin,TaskMax)

//Get Intersection
VAR tblIntersect=INTERSECT(tblPeriod,tblTask)
VAR noIntersect=COUNTROWS(tblIntersect)

//Is Task in Period
VAR IsInPeriod=IF(noIntersect>0,1)

RETURN
    IsInPeriod

So thank you for your time.

Kind Regards,

Lucian

[amitchandak]

@Lucian , refer this approach or attached file can help

Power BI: HR Analytics - Employees as on Date: https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...