Multivariable Matrix with User Input

[Anonymous]

See pictures below to help with explanation. 
Summary: I am receiveing a string of data with missing data points. I need to calculate the missing data points as well as react to user input. The user input could affect the calculation and/or the data shown. I've hit a dead end with both calculated columns (how can I capture user activity and use it in the calculated column formula?) as well as mutiple measure to represent the months (how do I get the beginning balance from the prior month/column?). 

Below is an example of the data I receive as well as a few examples showing ways the user wants the result displayed. Any ideas?

[sturlaws]

Hi, @Anonymous 

you could try to use a measure and a parameter for user input:

AmountMeasure =
VAR beginingbalance =
    CALCULATE (
        SUM ( accounts[amount] );
        FILTER (
            ALLEXCEPT ( accounts; accounts[account] );
            accounts[month]
                <= MIN ( [month] ) - 1
                && accounts[item] <> "income 1"
        )
    )
        + CALCULATE (
            SUM ( accounts[amount] );
            FILTER (
                ALLEXCEPT ( accounts; accounts[account] );
                accounts[month]
                    <= MIN ( [month] ) - 1
                    && accounts[item] = "income 1"
            )
        ) * [User input Value income 1]
VAR endingbalance =
    CALCULATE (
        SUM ( accounts[amount] );
        FILTER (
            ALL ( accounts );
            accounts[account] IN VALUES ( accounts[account] )
                && accounts[month] <= MIN ( [month] )
                && accounts[item] <> "income 1"
        )
    )
        + + CALCULATE (
            SUM ( accounts[amount] );
            FILTER (
                ALLEXCEPT ( accounts; accounts[account] );
                accounts[month] <= MIN ( [month] )
                    && accounts[item] = "income 1"
            )
        ) * [User input Value income 1]
RETURN
    SWITCH (
        TRUE ();
        MAX ( accounts[item] ) = "ending balance"; endingbalance;
        MAX ( accounts[item] ) = "begining balance"; beginingbalance;
        MAX ( accounts[item] ) = "income 1"; CALCULATE (
            SUM ( accounts[amount] );
            FILTER (
                ALLEXCEPT ( accounts; accounts[account]; accounts[month] );
                accounts[item] = "income 1"
            )
        ) * [User input Value income 1];
        SUM ( accounts[amount] )
    )

The measure in this code called [User input Value income 1] is from the parameter

[Anonymous]

This is great. I appreciate you working on this. 

I am using the measure now and it is acting unexpectedly. I am seeing amounts for Income 1 for every month, and not just the months where there is income. Also, the beginning balance looks like it is aggregating prior month ending balances for all accounts. 

[sturlaws]

I have tested it for the sample data you provided in the first post, perhaps you could provide a larger sample set? And preferably not provide data as screenshots, as I then have to manually input the values

[Anonymous]

Yes, you are correct. The measure works for the sample data above. I failed to include the year dimension in my sample data model. Here is the sample data with year included. 

Account;Item;Month;Year;Amount
1;Ending Balance;10;2018;30
1;Beginning Balance;11;2018;0
1;Expense 1;11;2018;-3
1;Income 1;11;2018;6
1;Income 2;11;2018;5
1;Ending Balance;11;2018;0
1;Beginning Balance;12;2018;0
1;Income 1;12;2018;7
1;Expense 1;12;2018;-8
1;Ending Balance;12;2018;0
1;Beginning Balance;1;2019;0
1;Expense 1;1;2019;-3
1;Income 2;1;2019;10
1;Income 3;1;2019;8
1;Ending Balance;1;2019;0
1;Income 2;12;2019;10
2;Ending Balance;10;2018;20
2;Beginning Balance;11;2018;0
2;Expense 1;11;2018;-3
2;Expense 2;11;2018;-4
2;Income 1;11;2018;5
2;Ending Balance;11;2018;0
2;Beginning Balance;12;2018;0
2;Expense 1;12;2018;-4
2;Income 1;12;2018;10
2;Ending Balance;12;2018;0
2;Beginning Balance;1;2019;0
2;Expense 3;1;2019;-3
2;Income 3;1;2019;15
2;Ending Balance;1;2019;0
2;Expense 3;11;2019;-3
3;Ending Balance;10;2018;30
3;Beginning Balance;11;2018;0
3;Expense 1;11;2018;-3
3;Expense 2;11;2018;-4
3;Income 1;11;2018;5
3;Ending Balance;11;2018;0
3;Beginning Balance;12;2018;0
3;Expense 1;12;2018;-3
3;Income 1;12;2018;10
3;Income 2;12;2018;5
3;Ending Balance;12;2018;0
3;Beginning Balance;1;2019;0
3;Expense 3;1;2019;-5
3;Income 1;1;2019;6
3;Ending Balance;1;2019;0
3;Expense 3;11;2019;-4
3;Income 3;12;2019;8

[sturlaws · Accepted solution]

I have uploaded a file showing how it could be done to google drive

[Anonymous]

This works exactly as I needed. Huge help! Thanks again.