Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
105 | |
93 | |
83 | |
64 |