Opening and Closing balances on monthly agreements

[tmears]

Hi there , I could really use some help with a specific issue. I've got a table that tracks various agreements by their IDs. Each agreement gets one hour of time per month, which decreases as it's used up. Unused time rolls over to the next month, building up to a max of 12 hours. If it hits 12 hours, it stays there until some time is used, then it goes back to adding one hour per month until it hits 12 again.   

I've been trying to get this system working for two days but keep running into problems. My first approach was to calculate the balance at the end of each month, but I'm getting stuck with circular references. I'm out of ideas and would love any suggestions on how to make this work. I've tried to show my issue in the attached Excel file. The opening balance shows the available hours from the previous month, which then adds the current month's end balance to figure out the hours available for this month, and so on.

[lbendlin · Accepted solution]

If it hits 12 hours, it stays there until some time is used, then it goes back to adding one hour per month until it hits 12 again.  

You lost me on this one.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[dufoq3]

Hi @tmears, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdK9CoMwFMXxVymZVXJvPjRjnyM4hk7tUPr+tEkXxXvOIErgh8r51+rETU5i7rf7493as70+t3G4iF/Uq/bn3+XHfZ+qU0LEJoEQtUkkpItwEQkLtUXGIthixSLaYsMi2aJgkW0hHpMVEDL8BggZvgDChvfAsOXB9P+fKXoiSpNUQkCSgRD7uyIWIMmEBUgyY3FKcu5n8zFj04AoNyxAlAUL1KTHBDVJlkdNsuVRk2x61CQZX8D6QuYfr4lXQvbXIznuLySAYLxn/wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Custom.Contract Month Number" = _t, #"Agreement id" = _t, Company_Name = _t, Month = _t, #"Monthly Support Hrs" = _t, #"Time Used" = _t, #"Month End Balance" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Custom.Contract Month Number", Int64.Type}, {"Agreement id", Int64.Type}, {"Month", type date}, {"Monthly Support Hrs", Int64.Type}, {"Time Used", type number}, {"Month End Balance", type number}}),
    F = (tbl as table, endBal_col as text)=>
        [ a = List.Buffer(Table.Column(tbl, endBal_col)),
          lg = List.Generate(
                    ()=> [ x = 0, openingBal = 0, availHours = openingBal + a{x} ],
                    each [x] < List.Count(a),
                    each [ x = [x]+1, openingBal = [availHours], availHours = List.Min({openingBal + a{x}, 12}) ],
                    each [Opening Balance = [openingBal], Available Hours = [availHours]] ),
          combined = Table.FromRecords(List.Transform(List.Zip({ Table.ToRecords(tbl), lg }), Record.Combine))
        ][combined],
    Ad_OpeningBalAndAvailHours = Table.Combine(Table.Group(ChangedType, {"Agreement id"}, {{"T", each F(_, "Month End Balance"), type table}}, 0)[T], Value.Type(Table.FirstN(ChangedType, 0) & #table(type table[Opening Balance=number, Available Hours=number], {})))
in
    Ad_OpeningBalAndAvailHours

[ZhangKun]

I don’t know if you are familiar with DAX, but this problem is better solved using DAX rather than Power Query, especially when the amount of data is large.

The expression of a DAX calculated column is written like this:

VAR vdt = 'TABLE NAME'[MONTH]
RETURN
CALCULATE(
SUM('TABLE NAME'[Month End Balance]),
'TABLE NAME'[MONTH] < vdt,
ALLEXCEPT('TABLE NAME', 'TABLE NAME'[Agreement id])
)

[Anonymous]

Hi @tmears ,

Thanks for reaching out to the Microsoft fabric community forum.

I would also take a moment to personally thank @lbendlin  and @Omid_Motamedise , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

As mentioned by Ibendlin, I also feel the same. The query you posted was not clear, so please follow the steps mentioned by Ibendlin for further questions.

From my understanding, the objective is to develop a table that precisely calculates the opening and closing balances for each agreement on a monthly basis.

The circular reference arises because you are trying to define both the opening and closing balance of a given month by cross-referencing calculations between months: you are trying to calculate the closing balance of the current month using the opening balance of the next month.

If I misunderstand your needs or you still have problems on it, please feel free to let us know. 

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

Thank you.

[Anonymous]

Hi @tmears ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[Anonymous]

Hello @tmears ,

I wanted to follow up on our previous suggestions regarding Opening and Closing balances on monthly agreements. We would love to hear back from you to ensure we can assist you further.

If my response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.

[Anonymous]

Hi @tmears ,

Thank you for your inputs @dufoq3 and @ZhangKun,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Omid_Motamedise · Accepted solution]

use the following formula in the custom column

List.Min({12,[Custome Conteract Month Number]})

and also 
List.Min({12,1+[Custome Conteract Month Number]})

[lbendlin · Accepted solution]

If it hits 12 hours, it stays there until some time is used, then it goes back to adding one hour per month until it hits 12 again.  

You lost me on this one.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...