Forum Discussion
Calculating Historic End of Month Balances Using Invoice Transactions and Payment Details
Greetings, Community.
Trying to solve the last piece of this project.
I have an EOM Open Balance Formula that currently looks like the below, but it isn't looking at the payment details that have transacted against the invoice transactions. It's only looking at where the balance is paid in full.
EOM Open Balance =
Var End_of_Month = SELECTEDVALUE(EOM_Aging_Balance[Date])
Var Open_Bal = FILTER(
Invoice_Transactions, Invoice_Transactions[Transaction Date] <= End_of_Month &&
OR(Invoice_Transactions[Paid in Full Date] > End_of_Month, Invoice_Transactions[Paid in Full Date] = BLANK())
)
Return
SUMX(Open_Bal, Invoice_Transactions[Calculated Amount])
Attached is a sample Power BI file with sample data. Ultimately, I am trying to get to the expectations below (this is considering that if any amount paid on the EOM date would not account for part of the balance by the end of that date). Essentially, and I believe, the total of all invoices up to the EOM date less the total of all payments up to the EOM date.
Expected Results
| EOM | EOM Balance |
| 7/31/2022 | $500.00 |
| 8/31/2022 | $3,500.00 |
| 9/30/2022 | $3,000.00 |
| 10/31/2022 | $5,000.00 |
Sample Data from File
Appreciate any and all help here.
I think I am on the right track here to expand upon my above DAX. I should be able to sum up all the transaction totals up to the EOM then sum up all the payments up to the EOM and subtract the two... I keep getting a blank result though, or no result.
EOM Open Balance = Var End_of_Month = SELECTEDVALUE(EOM_Aging_Balance[Date]) Var Open_Trans = FILTER( Invoice_Transactions, Invoice_Transactions[Transaction Date] <= End_of_Month) Var Open_Pay = FILTER( Payment_Details, Payment_Details[Payment Date] <= End_of_Month) Return SUMX(Open_Trans, Invoice_Transactions[Amount]) - SUMX(Open_Pay, Payment_Details[Payment Amount])
6 Replies
- amitchandak
Super User
Anonymous , check if closingbalancemonth can help https://youtu.be/yPQ9UV37LOU
Thank you, amitchandak. Reviewing and running tests on this now.
I think I am on the right track here to expand upon my above DAX. I should be able to sum up all the transaction totals up to the EOM then sum up all the payments up to the EOM and subtract the two... I keep getting a blank result though, or no result.
EOM Open Balance = Var End_of_Month = SELECTEDVALUE(EOM_Aging_Balance[Date]) Var Open_Trans = FILTER( Invoice_Transactions, Invoice_Transactions[Transaction Date] <= End_of_Month) Var Open_Pay = FILTER( Payment_Details, Payment_Details[Payment Date] <= End_of_Month) Return SUMX(Open_Trans, Invoice_Transactions[Amount]) - SUMX(Open_Pay, Payment_Details[Payment Amount])Hi Anonymous ,
The problem seems to be here:
VAR End_of_Month = SELECTEDVALUE ( EOM_Aging_Balance[Date] )If there is a filter context for EOM_Aging_Balance[Date] that is unique, the SELECTEDVALUE() function can capture it at this point, but when this measure is used alone, the filter context for EOM_Aging_Balance[Date] is empty, so the variable Open_Trans does not return any value either, so the next few variables are also empty.
You might consider adding the second parameter 'AlternateResult' to SELECTEDVALUE(), like this.
EOM Open Balance = VAR End_of_Month = SELECTEDVALUE ( EOM_Aging_Balance[Date] , MAX(EOM_Aging_Balance[Date]) ) VAR Open_Trans = FILTER ( Invoice_Transactions, Invoice_Transactions[Transaction Date] <= End_of_Month ) VAR Open_Pay = FILTER ( Payment_Details, Payment_Details[Payment Date] <= End_of_Month ) RETURN SUMX ( Open_Trans, Invoice_Transactions[Amount] ) - SUMX ( Open_Pay, Payment_Details[Payment Amount] )Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
