Find minutes taken to complete objective, multiple objectives per client

[OhMyDearLord]

Hi all,

I have a model (simplified version shown below)

I want to find out how many units of time it takes a client to complete an objective (As shown E2-E6).

Objectives are in the objective info table and each client can have multiple objectives. The time taken to complete an objective is in the work time info table.

I am looking for something that sums up all the time spent on an objective prior to the outcome date.

And as another part. The work a client does can fall under different work types and I would also like to show the total time taken to achieve the objective cut by that work type (clients can have multiple of the same objectives with different work types) (Shown in F3-F6)

Both work time and objective time tables are linked to a date table and a client table. Neither has the active date relationship. (the client table has this active relationship)

I think it's best to have these metrics as a table columns as opposed to measures. But am open if you see a different way.

The end goal is to use these measures to analyse how long it takes to complete objectives: averaged, difference between months etc.

Thanks!!!!

[Anonymous · Accepted solution]

Hi @OhMyDearLord ,

I have created a simple sample,please refer to my pbix file to see if it helps you.

Create 2 columns.

TIME spent to compelte = 
VAR _client = 'objective inofr'[Clients]
VAR _date = 'objective inofr'[Outcome date]
VAR _total = SUMX(FILTER(ALL('Table'),'Table'[Client]=_client&&'Table'[Workdate]<=_date),'Table'[Time spent])
RETURN
_total

TIME spent worktime to compelte = 
VAR _client = 'objective inofr'[Clients]
VAR _date = 'objective inofr'[Outcome date]
var _worktype='objective inofr'[Work type]
VAR _total = SUMX(FILTER(ALL('Table'),'Table'[Client]=_client&&'Table'[Workdate]<=_date&&'Table'[Work Type]=_worktype),'Table'[Time spent])
RETURN
_total

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[OhMyDearLord]

Hi @Anonymous 

Thanks for the solutiuon, works like a dream