DAX Workout 019 - Calculating Running Sums of Project Tasks

[tweinzapfel]

Everyone,

This is going to be a bit of a unique workout so we’ll see how it goes. And this one is around helping a Project Manager get a visual overview of how good (or not) tasks are being completed on time. This one is also being marked as “4 stars” as it may require a number of DAX measures depending on how you approach this.

Here is the data summary: you are asked to do an analysis of tasks for a project that was supposed to last three months (Jan - Mar 2023). The dataset provided includes the task name, start date, due date, and the actual completion date. Blank completion dates means the task is still open.

Data file is here:
Workout 019 data.xlsx (11.4 KB)

There are two objectives to this:

Objective #1: Build out several KPI cards that show:

1. • Total number of tasks;
2. • Number of tasks completed on time (either on or before the due date)
3. • Number of tasks completed after their due date
4. • Number of tasks still open (no due date yet)

This should be pretty straight forward.

Objective #2: The second objective is that the PM wants a visual representation over time as to how well tasks are being completed on time. They ask for an area chart that is “stacked” and shows the number of tasks completed past their due date, those completed on time, and then those that are still open. So looking at the chart provided, you can see the bottom area shaded slightly red represent tasks completed past their due date; the big middle green area are tasks completed on time and then the top blue area represents tasks that are still open.

And - this visual should represent a running sum of tasks. So the March area reflects all tasks that should have been completed by March (so inclusive of tasks due in January, February and March). To explain further - by the end of March, 64 tasks should have been completed. Of those, 37 were completed on time, 17 were completed past their due date, and 10 are still open. And the chart shows an overall progression.

So overall - it appears in this project most tasks are being completed on time yet a few remain open.

Bonus Objective #3: Create a tooltip that will display the information for any of the months. You will see that I removed the Y axis, but if anyone wants to see specific numbers, the tooltip will provide this. Now, in my tooltip I am only providing the running sum numbers hovering over March - but this could also be expanded to include that specific month’s information also.

Submission
Load the supplied data file, create your solution, and reply to this post. Upload a screenshot of your solution (or the PBI file) along with any DAX items. Please blur any solution or place it in a hidden section.

[renata_kulagowska]

Hello, My solution below. Hope I hide details correctly

Total number = DISTINCTCOUNT(‘Workout 019 data file 1’[Task Name])

[renata_kulagowska]

Sorry for previous hope this will work and blur it correctly
KPI
Total number = DISTINCTCOUNT(‘Workout 019 data file 1’[Task Name])
Late = CALCULATE(COUNT(‘Workout 019 data file 1’[Date dif]),‘Workout 019 data file 1’[Date dif]= “Late”)
On time = CALCULATE(COUNT(‘Workout 019 data file 1’[Date dif]),‘Workout 019 data file 1’[Date dif]= “On time”)
Open = CALCULATE(COUNT(‘Workout 019 data file 1’[Date dif]),‘Workout 019 data file 1’[Date dif]= “Not Completed”)
chart
Running Late =
CALCULATE([Late],FILTER(ALLSELECTED(‘Workout 019 data file 1’),‘Workout 019 data file 1’[Start Date] <=MAX(‘Workout 019 data file 1’[Start Date])))
Running On Time =
CALCULATE([On time],FILTER(ALLSELECTED(‘Workout 019 data file 1’),‘Workout 019 data file 1’[Start Date] <=MAX(‘Workout 019 data file 1’[Start Date])))
Running Open =
CALCULATE([Open],FILTER(ALLSELECTED(‘Workout 019 data file 1’),‘Workout 019 data file 1’[Start Date] <=MAX(‘Workout 019 data file 1’[Start Date])))

[HufferD]

@tweinzapfel,

I covered most aspects:

But I don’t have a separate line for Total Tasks.

[MubarakBabs]

I was slightly scared when I saw that this task was marked four stars. Happy to say that the fear was unfounded

The Measures

//first I created simple measures calculating the total tasks for each
Total Tasks = COUNTROWS('Task completion')

On TIME =
VAR ontime = FILTER(
  'Task completion',
  'Task completion'[Completed Date] <= 'Task completion'[Due Date] &&
   NOT( ISBLANK( 'Task completion'[Completed Date] ) )
) 

RETURN
COUNTROWS( ontime )

Past Due =
VAR pastdue = FILTER(
  'Task completion',
  'Task completion'[Completed Date] > 'Task completion'[Due Date]
) 

RETURN
COUNTROWS( pastdue )

Still Open =
VAR opentasks = FILTER(
  'Task completion',
    ISBLANK( 'Task completion'[Completed Date] )
) 

RETURN
COUNTROWS( opentasks )

//then the remaining measures contain the running totals
Cumulative total tasks = 
CALCULATE([Total Tasks],
          ALL('Task completion'[Due Month]),
          'Task completion'[Monthkey] <= MAX('Task completion'[Monthkey])
    )

Tasks On Time = 
CALCULATE([On Time],
          ALL('Task completion'[Due Month]),
          'Task completion'[Monthkey] <= MAX('Task completion'[Monthkey])
    )

Tasks Past Due = 
CALCULATE([Past Due],
          ALL('Task completion'[Due Month]),
          'Task completion'[Monthkey] <= MAX('Task completion'[Monthkey])
    )

Tasks Still Open = 
VAR calc =
CALCULATE([Still Open],
          ALL('Task completion'[Due Month]),
          'Task completion'[Monthkey] <= MAX('Task completion'[Monthkey])
    )
RETURN
IF(ISBLANK(calc), 0, calc)

For the tooltip, I simply created a tooltip age using a multi-row card visual containing all four cumulative measures

[shahadathossen.shajj]

DAX Measures

This text will be hidden

Number on time =
COUNTROWS (
    FILTER (
        'Workout 019 data file 1',
        'Workout 019 data file 1'[Completed Date] <> BLANK ()
            && 'Workout 019 data file 1'[Completed Date] - 'Workout 019 data file 1'[Due Date] < 1
    )
)
Number Past Due =
COUNTROWS (
    FILTER (
        'Workout 019 data file 1',
        'Workout 019 data file 1'[Completed Date] - 'Workout 019 data file 1'[Due Date] > 0
    )
)
Number Still Open =
COUNTROWS (
    FILTER (
        'Workout 019 data file 1',
        'Workout 019 data file 1'[Completed Date] = BLANK ()
    )
)
On Time =
CALCULATE (
    COUNTROWS (
        FILTER (
            'Workout 019 data file 1',
            'Workout 019 data file 1'[Completed Date] <> BLANK ()
                && 'Workout 019 data file 1'[Completed Date] - 'Workout 019 data file 1'[Due Date] < 1
        )
    ),
    DatesTable[Date] <= MAX ( DatesTable[Date] )
)
Past Due =
CALCULATE (
    COUNTROWS (
        FILTER (
            'Workout 019 data file 1',
            'Workout 019 data file 1'[Completed Date] - 'Workout 019 data file 1'[Due Date] > 0
        )
    ),
    DatesTable[Date] <= MAX ( DatesTable[Date] )
)
Still Open =
VAR __temp =
    CALCULATE (
        COUNTROWS (
            FILTER (
                'Workout 019 data file 1',
                'Workout 019 data file 1'[Completed Date] = BLANK ()
            )
        )
    )
RETURN
    IF ( __temp = BLANK () && MAX ( DatesTable[Month] ) = "Jan", 0, __temp )
Total Task =
CALCULATE (
    COUNTROWS ( 'Workout 019 data file 1' ),
    DatesTable[Date] <= MAX ( DatesTable[Date] )
)

[jsodhi]

Hi, Here is my Entry

DAX :

Click to See Dax Code

Ontime =

VAR res= CALCULATE(COUNTROWS(‘Workout 019 data file 1’), FILTER(‘Workout 019 data file 1’, ‘Workout 019 data file 1’[Completed Before DueDate]= “True”))

Return
res

Ontime RT = CALCULATE([Ontime], ‘Calendar’[Date]<=SELECTEDVALUE(‘Calendar’[Date]))

Past DueDate =

VAR res= CALCULATE(COUNTROWS(‘Workout 019 data file 1’), FILTER(‘Workout 019 data file 1’, ‘Workout 019 data file 1’[Completed Before DueDate]= “False”))
Return
res

PastDueDate RT = CALCULATE([Past DueDate], ‘Calendar’[Date]<=SELECTEDVALUE(‘Calendar’[Date]))

Open Tasks = [Tasks]- [Ontime]-[Past DueDate]

Open Tasks RT = CALCULATE([Open Tasks], ‘Calendar’[Date]<=SELECTEDVALUE(‘Calendar’[Date]))

Tasks = COUNTROWS(VALUES(‘Workout 019 data file 1’[Task Name]))
Tasks RT = CALCULATE([Tasks], ‘Calendar’[Date]<=SELECTEDVALUE(‘Calendar’[Date]))

[ckozey]

Hi, this is the first time I’m making a workout, so I hope I did everything the correct way.

Workout 19 Image.docx (51.2 KB)

Total Task =
CALCULATE (
COUNTROWS ( ‘Workout 019 data file 1’ ),
Date_table[Date] <= MAX ( Date_table[Date] ))

Completed_on_Time =
CALCULATE([Nr_of_Tasks],
FILTER(‘Workout 019 data file 1’,
‘Workout 019 data file 1’[Completed Date] <= ‘Workout 019 data file 1’[Due Date]
),
‘Workout 019 data file 1’[Completed Date] <> BLANK()
)

Completed_after_Due_date =
CALCULATE([Nr_of_Tasks],
FILTER(‘Workout 019 data file 1’,
‘Workout 019 data file 1’[Completed Date] > ‘Workout 019 data file 1’[Due Date]
),
‘Workout 019 data file 1’[Completed Date] <> BLANK()
)

Still Open =
[Total Tasks to date]-[Tasks Completed on Time]-[Tasks Completed past Time]

Total Tasks to date =
VAR MaxDate = MAX ( Date_table[Month] )
RETURN
CALCULATE (
[Nr_of_Tasks],
ALL ( Date_table),
‘Workout 019 data file 1’[Month_nr] <= MaxDate
)

Tasks Completed on Time =
VAR MaxDate = MAX ( Date_table[Month] )
RETURN
CALCULATE (
[Completed_on_Time],
ALL ( Date_table),
‘Workout 019 data file 1’[Month_nr] <= MaxDate
)

Tasks Completed past Time =
VAR MaxDate = MAX ( Date_table[Month] )
RETURN
CALCULATE (
[Completed_after_Due_date],
ALL ( Date_table),
‘Workout 019 data file 1’[Month_nr] <= MaxDate
)

[mbraun]

answer:

DAX Workout No19 MB.pbix (333.6 KB)