Time spent calculation in this model..

[R_FROM_G]

Hello,

I have to create a report that visualize the estimated time and actual time spent on a project. A project has one or more milestones. Each milestone has one or more tasks. Users normally write their time spent on a task but sometimes they write their time spent on the milestone level. There are 4 tables in the model: Project, Milestone, Task and Timetrack. Each table has a unique id. The TimeTrack table has a column 'objectid' that either contains the key value of the task or the key value of the milestone. I setup a simple matrix:

First I see some some strange behaviour: a blank project was displayed on the first row of the table when I added the 'Actual' column to the matrix. The value of 960 is the time spent that written on the milestone level for Milestone 3.

But what I really want is the following:

The time spent that was written on the milestone level should be displayed on the total row for the milestone (in this case milestone 3) . In this example the time spent for task 8 and 9 should be empty because there was no time spent written on the task level. But in case there was the time spent written on the task level should add up to the total value of the Milestone.

(Some additional info: although it is not modelled yet.. but the TimeTrack will be filtered by a date slicer eventually.)

Here is a link to the pbix: https://1drv.ms/u/s!Ak-rq-1Mea_mgfZif6ASH6TmecGNBw?e=plH5dT

How can this be accomplished?

Best regards,

Ruud de Heij

[jgeddes · Accepted solution]

The issue is your relationship between the timeTaskId and the milestoneId. You would have to change the organization of your tables to allow for both milestone or task entries with valid relationships.
Alternatively, you can try a measure like...

Amended Actual Time Spent = 
var _milestoneCalc =
CALCULATE(
    SUM(TimeTrack[Duration]),
    REMOVEFILTERS(TimeTrack),
    TREATAS(VALUES(Milestone[MilestoneId]), TimeTrack[ObjectId])
)
var _taskCalc =
CALCULATE(
        SUM(TimeTrack[Duration]),
        REMOVEFILTERS(TimeTrack),
        TREATAS(VALUES(Task[TaskId]), TimeTrack[ObjectId])
    )
Return
SWITCH(
    TRUE(),
    ISINSCOPE(Task[TaskTitle]),
    _taskCalc,
    ISINSCOPE(Milestone[MilestoneTitle]),
    IF(
        ISBLANK(_milestoneCalc),
        _taskCalc,
        _milestoneCalc
    ),
    _taskCalc
)

[R_FROM_G]

Thank you !

I made a small change to the code so that the totals are correctly displayed:

Amended Actual Time Spent =

var _milestoneCalc =

CALCULATE(

    SUM(TimeTrack[Duration]),

    REMOVEFILTERS(TimeTrack),

    TREATAS(VALUES(Milestone[MilestoneId]), TimeTrack[ObjectId])

)

var _taskCalc =

CALCULATE(

        SUM(TimeTrack[Duration]),

        REMOVEFILTERS(TimeTrack),

        TREATAS(VALUES(Task[TaskId]), TimeTrack[ObjectId])

    )

Return

SWITCH(

    TRUE(),

    ISINSCOPE(Task[TaskTitle]), _taskCalc,

    ISINSCOPE(Milestone[MilestoneTitle]),

    IF(

        ISBLANK(_milestoneCalc),

        _taskCalc,

        _milestoneCalc + _taskCalc

    ),

    _milestoneCalc + _taskCalc

)