Hey guys. I have a challenge that has me stuck. I have a data dump of bank transactions. The simplified data looks like this:
<tbody>
</tbody>
My goal is to create a helper column that orders the transactions like this:
<tbody>
</tbody>
My ultimate goal is to get a working DAX formula but a regular formula might help me get there. I cannot think of a solution that doesn't throw a circular reference error. My first attempt was this:
=IF([OpeningLedgerBal]-[Debit Amt]+[Credit Amt]=[RunningLedgerBal],"1",LOOKUPVALUE([Order#],[RunningLedgerBal],[CalcBegLedgerBal])+1)
Where [CalcBegLedgerBal] was a helper column. Any ideas? I would appreciate any input at all!
OpeningLedgerBal | Debit | Credit | EndRunningLedger |
250 | 10 | 320 | |
250 | 10 | 340 | |
250 | 10 | 300 | |
250 | 100 | 350 | |
250 | 10 | 330 | |
250 | 10 | 310 | |
250 | 10 | 290 |
<tbody>
</tbody>
My goal is to create a helper column that orders the transactions like this:
OpeningLedgerBal | Debit | Credit | EndRunningLedger | Order# |
250 | 10 | 320 | 4 | |
250 | 10 | 340 | 2 | |
250 | 10 | 300 | 6 | |
250 | 100 | 350 | 1 | |
250 | 10 | 330 | 3 | |
250 | 10 | 310 | 5 | |
250 | 10 | 290 | 7 |
<tbody>
</tbody>
My ultimate goal is to get a working DAX formula but a regular formula might help me get there. I cannot think of a solution that doesn't throw a circular reference error. My first attempt was this:
=IF([OpeningLedgerBal]-[Debit Amt]+[Credit Amt]=[RunningLedgerBal],"1",LOOKUPVALUE([Order#],[RunningLedgerBal],[CalcBegLedgerBal])+1)
Where [CalcBegLedgerBal] was a helper column. Any ideas? I would appreciate any input at all!