Show Task on Table Based on Selected Week

[KJChin]

Hi, 

I am working on building table that show all tasks with Status "Not Start", "On Hold", "In Progress" and closed task (by Closed Date) based on selected week. 

Basically I have a project table, with structure like below:

Task	Created Date	Start Date	Closed Date	Target Date	Status
A	MM/DD/YYYY	-	-	-	Not Started
B	MM/DD/YYYY	-	-	-	On Hold
C	MM/DD/YYYY	MM/DD/YYYY	-	MM/DD/YYYY	In Progress
D	MM/DD/YYYY	MM/DD/YYYY	MM/DD/YYYY	MM/DD/YYYY	Closed

And I created a Calendar table, with column Date (extracted from Min(Created Date) and Max(Target Date)) and column YearWeek (WW'YY). And I connect it (column Date) to project table (column Created Date).

The issue that I encounter is that, the visual table will show almost every task because of this relationship.

So I try to use kinda 'cheatsheet' way by creating a calculated column at the project table:

ReferenceDate =
VAR TaskStatus = Project[Status]
RETURN
    SWITCH (
        TRUE(),
        TaskStatus IN { "Not Started", "In Progress", "On Hold" }, Today(),
        TaskStatus = "Closed", WorkItems[Closed Date],
        BLANK()
    )

When I select current week, yup, the task that match the condition did appear. Yet, when other week is selected, it will be blank. 

Is there anyone ever encounter this before or have experience with it when develop gantt chart? How you overcome it?

Any guidance will be helpful. Thanks in advance. 

[Anonymous · Accepted solution]

Hi, @KJChin 

We need to make sure that the logic of the ReferenceDate covers all status tasks, and use Start Date instead of Today() for tasks that are not started, in progress, and paused. Using the Today() function restricts the task to be displayed only in the current week, and not for other weeks.

I used the following example data:

Create a new calculated column: Create a new calculated column in the Project table, ReferenceDate, which is used to determine which date to use as the reference date based on the status of the task.

ReferenceDate = 
VAR TaskStatus = Project[Status]
RETURN
    SWITCH (
        TRUE(),
        TaskStatus IN { "Not Started", "On Hold" }, Project[Created Date],
        TaskStatus = "In Progress", Project[Start Date],
        TaskStatus = "Closed", Project[Closed Date],
        BLANK()
    )

To establish a relationship between a calculated column and a date table:

To create a TaskCount measure:
We need to make sure that the filter correctly covers all tasks and is dynamically associated with the selected week.

TaskCount = 
CALCULATE(
    COUNTROWS(Project),
    FILTER(
        Project,
        NOT(ISBLANK(Project[ReferenceDate])) &&
        Project[ReferenceDate] >= MIN(Calendar[Date]) &&
        Project[ReferenceDate] <= MAX(Calendar[Date])
    )
)

Here are the results:

Best Regards

Jianpeng Li

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