Conditional Inventory Change - Recursion - Impossible?

[CPL_HK]

Dear PowerBI Community,

I have been doing quite some research on this, and I already understand, that my problem is not easy to solve in DAX due to limitations with regards to recursive calculations. I would very much appreciate any guidance.

Real life background

An employee of a company has a "working time account". Each working day is one row, including a rank [Rank], with 1 being the first working day, 2 the second working day etc. Each row contains the actual number of hours worked of this employee [Productive_Hours]. If the employee works more than 8 hours, additional time will be added [change WTA] to his working time account [WTA end of day], unless the account exceeds a certain limit x. If the employee works less than 8 hours, the working day is filled up [change WTA] with time frome the working time account to the level of 8 hours, until the account is empty [WTA beginning of day] = [WAT end of day in the] of the row with one [Rank] lower than the current row. In other words, the account is changing not only depending on the deviation between productive and target hours, but also based on the number of hours in the account of the previous day. I both would like to calculate for each day how many hours are in the account and how the account changes (given the conditions that the account cannot become empty or too big).

Specific programming problem
How can I lookup a value in the table (different row and column than current one) which is in turn calculated based on the current column in a different row? [WTA BOD] is currently hardcoded to 5 and 0 (in case of first date) respectively, the challenge now is to replace this hardcoded 5 with a formula which selects [WTA EOP] from the earlier rank (with the caveat that this is again dependent on column [WTA BOD] in another row, causing a circular dependency when using calculate/filter functions to lookup the value). 

Excel screenshot: https://ibb.co/712v3BZ

PBX: https://workupload.com/file/RvfE5JH7

Does anyone have any guidance on how to approach this issue? I just can't believe that there is no way to do this.

Best,
Christian

[v-lili6-msft]

hi @CPL_HK 

The file you uploaded missed, please re-upload.

For your case, please share a sample pbix file and your expected output.

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Regards,

Lin

[CPL_HK]

Hi  @v-lili6-msft  - thank you for the comments. I repaired the link, some formatting issue crashed it. 

Here you can find a simple sample pbx file.  [WTA BOD] is currently hardcoded to 5 and 0 (in case of first date) respectively, the the only challenge now is to create a formula which selects [WTA EOP] from the earlier rank (with the caveat that this is again dependent on column [WTA BOD] in another row, causing a circular dependency when using calculate/filter functions to lookup the value). 

https://workupload.com/file/RvfE5JH7

Thank you so much everybody!

[v-lili6-msft]

hi @CPL_HK 

What is the expected output for this sample data?

Could you explain it with some example based on the data.

From your description, i also think it is a circular dependency problem.

Regards,

Lin

[CPL_HK]

@v-lili6-msft  The expected output is: For each row of WTA BOD, if [day rank] is bigger than 1, find the value of [WTA EOD] of the previous day (= current row [day rank]-1). So for example, [WTA BOD] in row two (Day Rank = 2) is equal to [WTA EOD] in row 1 (Day Rank = Current Row Day Rank less 1). In the pbx file [WTA BOD] is hardocded just to have anything in there but the calculation is not correct (here I am looking for the formula). In the excel screenshot you see the correct calculation, e.g. E4 = G3, E5 = G4 etc. 

[CPL_HK]

Any ideas anybody?

[CPL_HK]

*push*