Take the first values up to a certain level

[Anonymous]

Hello,

I have a rather tricky question I cannot solve myself.

I have this kind of recordings, which describe time slots  :

Beginning	Category	Duration (hours)
23/01/2020 09:00	blabla	6
23/01/2020 15:00	blibli	3
23/01/2020 18:00	bloblo	2

I have to create reports that only take 7.5 hours maximum per day so I would like to add a column that recalculates the durations so that their total reaches 7.5, starting with the first ones. Here's how it would look like :

Exemple :

Beginning	Category	Duration (hours)	Duration (2)
23/01/2020 09:00	blabla	6	6
23/01/2020 15:00	blibli	3	1.5
23/01/2020 18:00	bloblo	2	0

I can do it by extracting the data and use Excel mais I would love to have it displayed directly in PowerBi.

Thank you for your help,

[camargos88 · Accepted solution]

Hi @Anonymous,

In order to make it work, replace the bold part (it's my last step name).

Take a look at yours steps, get the name and replace it in the function. 

If possible share you m code, so I can replace it with your last step name.

[Anonymous · Accepted solution]

Hello,

thank you so much for your answer. DAX is ideed an easier way for me to complete the task.

I dont have an index so I tested on the beginning date this way :

Measure = 
var _previousduration= CALCULATETABLE(VALUES('Table'[Duration (hours)]),FILTER(ALL('Table'),'Table'[Beginning]<MAX('Table'[Beginning])))Return
IF(SUMX(FILTER(ALL('Table'),'Table'[Beginning]<=MAX('Table'[Beginning])),'Table'[Duration (hours)])<=7.5,MAX('Table'[Duration (hours)]),7.5-SUMX(_previousduration,'Table'[Duration (hours)]))

It seemms to work except if I have only one task longer than 7.5h. In that case, the result is 0 (should be 7.5).

Is there any way you can think of that would solve the issue ?

Thank you

[v-kelly-msft]

Hi @Anonymous ，

You can also try to use dax expression to achieve it:

Create 2 measures as below:

Measure = 
var _previousduration= CALCULATETABLE(VALUES('Table'[Duration (hours)]),FILTER(ALL('Table'),'Table'[Index]<MAX('Table'[Index])))Return
IF(SUMX(FILTER(ALL('Table'),'Table'[Index]<=MAX('Table'[Index])),'Table'[Duration (hours)])<=7.5,MAX('Table'[Duration (hours)]),7.5-SUMX(_previousduration,'Table'[Duration (hours)]))

Duration (2) = 
IF('Table'[Measure]>0,'Table'[Measure],0)

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous · Accepted solution]

Hello,

thank you so much for your answer. DAX is ideed an easier way for me to complete the task.

I dont have an index so I tested on the beginning date this way :

Measure = 
var _previousduration= CALCULATETABLE(VALUES('Table'[Duration (hours)]),FILTER(ALL('Table'),'Table'[Beginning]<MAX('Table'[Beginning])))Return
IF(SUMX(FILTER(ALL('Table'),'Table'[Beginning]<=MAX('Table'[Beginning])),'Table'[Duration (hours)])<=7.5,MAX('Table'[Duration (hours)]),7.5-SUMX(_previousduration,'Table'[Duration (hours)]))

It seemms to work except if I have only one task longer than 7.5h. In that case, the result is 0 (should be 7.5).

Is there any way you can think of that would solve the issue ?

Thank you

[camargos88]

Hi @Anonymous ,

Try this code for a custom column on Power Query:

let
_dateTime = [Beginning],
_currentValue = [#"Duration (hours)"],
_day = Table.SelectRows(#"Changed Type",
each
Date.From([Beginning]) = Date.From(_dateTime) and
[Beginning] <= _dateTime
)[#"Duration (hours)"],
_result =
List.Accumulate(_day, 7.5, (s,c) => s - c) in
if _result > 0 then _currentValue
else if _result + _currentValue > 0 then _result + _currentValue
else 0

change the bold part for your last step name.

[Anonymous]

Hello, thank you very much for your quick answer.

I tried the solution but can't make it work.

I can add a custom column with the formula you provided but I don't understand what I should replace "changed type" with, nor do I get how to use the formula with "addTable" below.

Could you just be a bit more descriptive please ?

best regards

[camargos88 · Accepted solution]

Hi @Anonymous,

In order to make it work, replace the bold part (it's my last step name).

Take a look at yours steps, get the name and replace it in the function. 

If possible share you m code, so I can replace it with your last step name.

[Anonymous]

Hello Camargos,

I think your solution works. I will mark it as a solution as soon I have an answer to the question I asked to the second person that proposed me a solution (with DAX, which is a bit easier for the newbie I am).

Thank you again,

[v-kelly-msft · Accepted solution]

Hi @Anonymous ,

You can add a criteria in the if function to achieve it.

Measure = 
var _previousduration= CALCULATETABLE(VALUES('Table'[Duration (hours)]),FILTER(ALL('Table'),'Table'[Index]<MAX('Table'[Index])))Return
IF(MAX('Table'[Duration (hours)])>=7.5,7.5,IF(SUMX(FILTER(ALL('Table'),'Table'[Index]<=MAX('Table'[Index])),'Table'[Duration (hours)])<=7.5,MAX('Table'[Duration (hours)]),7.5-SUMX(_previousduration,'Table'[Duration (hours)])))

Duration (2) = 
IF('Table'[Measure]>0,'Table'[Measure],0)

Finally you will see:

For the modified .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hello

thank you very much. It works !