Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I am trying to take the list of transactions on the left and create a "daily" report similar to what is in the right. The user could select a day of transactions they wish to see (normally it is the previous day) and then see the transactions for that day with the Beginning Balance being the Ending Balance from the previous day and the ending balance being the ending balance for that day. Help?
Thanks in advance.
Solved! Go to Solution.
See if this works. First the model:
And to return the maximum description per day, I've added the number reference in the Dim table as follows:
And with these measures:
Sum Amount = SUM('Table'[ Amount ])
Sum Ending Balance = SUM('Table'[Ending Balance])
Description Number =
CALCULATE (
MAX ( 'Dim Description'[Transaction Number] ),
RELATEDTABLE ( 'Table' )
)
Previous Day Balance =
CALCULATE (
[Sum Ending Balance],
FILTER (
ALL ( 'Table' ),
'Table'[Date]
= SELECTEDVALUE ( 'Date Table'[Date] ) - 1
&& [Description Number]
= CALCULATE ( [Description Number], ALLEXCEPT ( 'Table', 'Date Table'[Date] ) )
)
)
Current Day Ending Balance =
CALCULATE (
[Sum Ending Balance],
FILTER (
ALL ( 'Table' ),
'Table'[Date] = SELECTEDVALUE ( 'Date Table'[Date] )
&& [Description Number]
= CALCULATE ( [Description Number], ALLEXCEPT ( 'Table', 'Date Table'[Date] ) )
)
)
you get
I've attached the sample PBIX file
Proud to be a Super User!
Paul on Linkedin.
It would be helpful if you could share sample data to work on!
Proud to be a Super User!
Paul on Linkedin.
Here you go!
Operating Account | |||||
Transaction | Description | Date | Amount | Ending Balance | |
Beginning Balance | Beginning Balance | 11/01/2021 | 70,302.11 | 70,302.11 | |
Deposit | Transaction 1 | 11/01/2021 | 5,865.88 | 76,167.99 | |
Deposit | Transaction 2 | 11/01/2021 | 3,647.03 | 79,815.02 | |
Deposit | Transaction 3 | 11/01/2021 | 64.09 | 79,879.11 | |
Deposit | Transaction 4 | 11/01/2021 | 854.08 | 80,733.19 | |
Deposit | Transaction 5 | 11/02/2021 | 617.35 | 81,350.54 | |
Deposit | Transaction 6 | 11/02/2021 | 22.73 | 81,373.27 | |
Deposit | Transaction 7 | 11/02/2021 | 97.56 | 81,470.83 | |
Deposit | Transaction 8 | 11/02/2021 | 112.85 | 81,583.68 | |
Deposit | Transaction 9 | 11/02/2021 | 77.35 | 81,661.03 | |
Deposit | Transaction 10 | 11/02/2021 | 1,373.82 | 83,034.85 | |
Misc | Transaction 11 | 11/03/2021 | 300.00 | 83,334.85 | |
Revenue | Transaction 12 | 11/03/2021 | 572.82 | 83,907.67 | |
Revenue | Transaction 13 | 11/03/2021 | 3,508.76 | 87,416.43 | |
Revenue | Transaction 14 | 11/03/2021 | 14.53 | 87,430.96 | |
Revenue | Transaction 15 | 11/03/2021 | 33,085.89 | 120,516.85 | |
Revenue | Transaction 16 | 11/03/2021 | 120.20 | 120,637.05 |
See if this works. First the model:
And to return the maximum description per day, I've added the number reference in the Dim table as follows:
And with these measures:
Sum Amount = SUM('Table'[ Amount ])
Sum Ending Balance = SUM('Table'[Ending Balance])
Description Number =
CALCULATE (
MAX ( 'Dim Description'[Transaction Number] ),
RELATEDTABLE ( 'Table' )
)
Previous Day Balance =
CALCULATE (
[Sum Ending Balance],
FILTER (
ALL ( 'Table' ),
'Table'[Date]
= SELECTEDVALUE ( 'Date Table'[Date] ) - 1
&& [Description Number]
= CALCULATE ( [Description Number], ALLEXCEPT ( 'Table', 'Date Table'[Date] ) )
)
)
Current Day Ending Balance =
CALCULATE (
[Sum Ending Balance],
FILTER (
ALL ( 'Table' ),
'Table'[Date] = SELECTEDVALUE ( 'Date Table'[Date] )
&& [Description Number]
= CALCULATE ( [Description Number], ALLEXCEPT ( 'Table', 'Date Table'[Date] ) )
)
)
you get
I've attached the sample PBIX file
Proud to be a Super User!
Paul on Linkedin.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
84 | |
83 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |