Checkbook Type of Report

[Razorbx13]

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.

[PaulDBrown]

It would be helpful if you could share sample data to work on!

[Razorbx13]

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

[PaulDBrown · Accepted 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