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.
Hi all,
I hope this is not a basic question, but I am new to PowerBI, and I've been looking for a long time with no solutions here.
My problem is as follows:
I have a number of accounts (10 or so) for which I have transactional data stored in a General Ledger table [PostGL]. The table has the the following fields (actual field names in parenthesis):
I also have an Accounts table with the following fields:
In my model I have created a bi-directional link between 'PostGL'[AccountLink] and 'Accounts'[AccountLink].
I am trying to build a table which shows the cumulative totals for each day of the current month. I have built a measure which sums the account balance as follows:
Account Balance =
SUM('PostGL'[Debit]) - SUM('PostGL'[Credit])
and a second measure which calculates (or is supposed to calculate) the cumulative balance as follows:
Account Balance Cumulative =
VAR MaxDate = today()
RETURN
CALCULATE (
[Account Balance],
PostGL[TxDate] <= MaxDate,
ALL( PostGL[TxDate] )
)
This works to the extent that when I build a visual, the correct account balance per account can be seen, however this amount is shown against each date, and I am at a loss as to how to show the account balance as at that specific date.
In otherwords I need this
to be this:
Not sure what in the world I'm doing wrong and would appreciate any assistance.
Many thanks in advance
Solved! Go to Solution.
You are seeing this behaviour because you are using Today() in your MaxDate variable. You are effectively using the same date for every column which is why you are seeing the same value in every row.
I think if you change this to the following your calc should work:
VAR MaxDate = MAX( 'PostGL'[txDate] )
You are seeing this behaviour because you are using Today() in your MaxDate variable. You are effectively using the same date for every column which is why you are seeing the same value in every row.
I think if you change this to the following your calc should work:
VAR MaxDate = MAX( 'PostGL'[txDate] )
Thank you so much! This fixed it
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |