Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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
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.
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
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
This works exactly as I needed. Huge help! Thanks again.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |