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.
Table 'GL Account Balances by Date' contains general ledger balances for the entire chart of accounts, with one balance per month.
However, the source table contains no values when no transactions took place in a given month. Thus, account 1083, for instance, shows no Balance after 3/1/22, even though in reality it still has a Balance of $18,200.
All months prior to the month containing the last transaction do populate with zero values, as desired. But 4/1/22, 5/1/22, and 6/1/22 are all missing.
What is the best way to fix this? I see four options:
1. Ignore this system-generated Table and instead create a new one based on a separate Transactions table.
2. Use DAX to create a Measure within the 'GL Account Balances by Date' Table. I have attempted this but have been unsuccessful, likely because I do not have any blank records available for the Measure to populate.
3. Use DAX to create a brand new Table, joining 'GL Account Balances by Date' with my DATE TABLE. Note this would have to consider every combination of Date, CompanyCode, GLAccountNumber, GLDepartmentCode, and GLSubAccountNumber.
4. Use Power Query to append Balances with a full DATE TABLE, filling down values.
Your help is greatly appreciated.
Solved! Go to Solution.
Hi @LobsterLegend , Don't complicate your requirement. Just create a running total on the Balance Amount. Use the Calendar Date to calculate and display the running total. Use the quick measure option to create a Running total.
Hope this helps!
Thank you, dhruvinushah. The existence of an Account Balance table in the source SQL database sent me on a wild goose hunt.
Hi @LobsterLegend , Don't complicate your requirement. Just create a running total on the Balance Amount. Use the Calendar Date to calculate and display the running total. Use the quick measure option to create a Running total.
Hope this helps!
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |