Multiple registrations should not exceed an estimate

[ThomasWeppler]

Hi power Bi community

I want to count how many hours employees works within an estimate.

So I have an assingment table where all the diffrents assingments are and the hours we estimate will be spent on them.

We also have another registration table with all the diffrent times their has been worked on an assingment, the date and for how long.

So the first table might look likke this

assingmentid; estimate 

1                    ; 3

2                    ; 5

3                    ; 20

4                    ; 10

I also have another table with all the resitrations  it could look like this

index; assingmentid; hours; date
1       ;1                     ;2        ; 01-01-2022

2       ;1                     ;4        ; 01-03-2022

3       ;1                     ;2        ; 01-06-2022

4       ;2                     ;7        ;02-03-2022

5       ;2                     ;5        ;14-05-2022

6       ;3                     ;8        ;05-05-2022

7       ;3                     ;4        ;08-05-2022

8       ;4                     ;6        ; 03-01-2022

I want to either add a colum or metrix which calculate the number of hours within this estimate.

So for assingment 1 we would have 2 hours 01-01-2022, 1 hour 01-01-2022 since we have already used 2 of the three hours from the estimate in january and 0 hours 01-06-2022 since now we have used all of the estimate.

I have some ideas to solve it, but they would make the entire calculation very slow.

Do you guys have any ideas on how I could solve it? All help is appreciated.

[v-yadongf-msft]

Hi @ThomasWeppler ,

This is my test table1:

Table2:

Create two columns:

Estimate = LOOKUPVALUE('Table1'[Estimate],'Table1'[Assingmentid],'Table2'[Assingmentid])

hours within estimate = IF([Estimate] <= [Hours],[Estimate],[Hours])

Is this the result you want?

Best regards,

Yadong Fang

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

[ThomasWeppler]

Hi @v-yadongf-msft 

Thanks for the quick response.

No this doesn't solve my problem. If you look at your table and index 2 we have already used most of the hours in the estimate in index 1 so the hours within estimate here should be 1. and if we look at index 3 we have already used all the hours in index 1 and 2 so the hours within estimate should be 0.

[v-yadongf-msft · Accepted solution]

Hi @ThomasWeppler ,

Please create following columns:

Estimate = LOOKUPVALUE('Table1'[Estimate],'Table1'[Assingmentid],'Table2'[Assingmentid])

cumulative = CALCULATE(SUM('Table2'[Hours]),FILTER('Table2','Table2'[Assingmentid] = EARLIER('Table2'[Assingmentid]) && 'Table2'[Date] <= EARLIER('Table2'[Date])))

vious_cumulative = CALCULATE(MAX('Table2'[cumulative]),FILTER('Table2','Table2'[Assingmentid] = EARLIER('Table2'[Assingmentid]) && 'Table2'[Index] = EARLIER('Table2'[Index])-1))

Hours_within_estimate = 
 SWITCH(
    TRUE(),
    'Table2'[cumulative] <= 'Table2'[Estimate],'Table2'[cumulative],
    'Table2'[cumulative] > 'Table2'[Estimate] && 'Table2'[Estimate] - 'Table2'[Previous_cumulative] > 0, 'Table2'[Estimate] - 'Table2'[Previous_cumulative],
    0)

The result you want:

Best regards,

Yadong Fang

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

[ThomasWeppler]

Thanks. This solved my problems and I am repeatedly impressed by your guys DAX skills.


But for anyone who has similar problem I made a couple of changes to the code that you might help you.

First of all I changed date to index in cumulative. This helped me solve the problem when I had multiple registration on the same date. (Note it was not possible to see this was a problem or that the index was sorted after registration time in the problem I posted) 

cumulative = CALCULATE(SUM('Table2'[Hours]),FILTER('Table2','Table2'[Assingmentid] = EARLIER('Table2'[Assingmentid]) && 'Table2'[Date] <= EARLIER('Table2'[Date])))

I also deleted the previous_ cumulative column and changed the Hours_within_estimate colum to this: 

Hours_within_estimate =

var _within =

if(Registrationen[Estimate] >= Registrationen[cumulative], Registrationen[minutes],

Registrationen[Estimate] - Registrationen[cumulative])

return

if(_within < 0, 0, _within)

To optimize it all a bit.

Thanks again @v-yadongf-msft for the help.