DAX - sort order transactions by logic

[ejheflin]

Hey guys. I have a challenge that has me stuck. I have a data dump of bank transactions. The simplified data looks like this:

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]OpeningLedgerBal[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]EndRunningLedger[/TD]
[/TR]
[TR]
[TD]250
[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]320[/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]340[/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]350[/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]330[/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]310[/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]290[/TD]
[/TR]
</tbody>[/TABLE]

My goal is to create a helper column that orders the transactions like this:
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]OpeningLedgerBal[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]EndRunningLedger[/TD]
[TD]Order#[/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]320[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]340[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]300[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]350[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]330[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]310[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]290[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[Martvg]

Dear Ejheflin,

Is it always in the context where first a credit appears (only one?), followed by a bunch of debit transactions, while it all has the same openingledger?

Best regards,

 

[ejheflin]

Dear Ejheflin,

Is it always in the context where first a credit appears (only one?), followed by a bunch of debit transactions, while it all has the same openingledger?

Best regards,

Unfortunately no. The detail can contain unlimited debits/credits in any order. The OpeningLedgerBal is all the same because it is the balance at the beginning of the month.

 

[Martvg]

Hi again,

I couldn't come up with a formula. However, I liked the challenge your question had (as I still have plenty to learn), so I turned to VBA.

For this to work (I assume your data is currently in PowerPivot), you must export your data out of PowerPivot to an Excel worksheet.

The worksheet should be built as follows:
-Column A containing the Openingledgerbal.
-Column B containing the Debit.
-Column C containing the Credit.
-Column D containing the Endrunningledger.
-Column E and F must be empty (except for the first row).
-The first Row of column a,b,c,d,e,f must be filled (with however you want to name the column).

Furthermore, the value in endrunningledgar must be exactly the value of the previous endrunningledgar + Credit or - Debit of the current transaction (even a penny in difference will cause this code to fail).
E.g. Previous endrunningledgar was 321.21, credit on current transaction is 10.00, current endrunningledgar must be exactly 331.21.

Lastly, only one of the following statements should be true and may only occur once:
previous endrunningledgar + credit current transaction = current endrunningledgar.
previous endrunningledgar - debit current transaction = current endrunningledgar.

If these statements are correct twice in the given list, the code will only take notice of the first occurrence.
E.g.:
Previous endrunningledgar = 350
Transaction 1 Debit 10
Transaction 2 Credit 10
Transaction 1 endrunningledgar = 340
Transaction 2 endrunningledgar = 360

So both statements are correct, the code misses the second transaction which might have occurred before the second transaction.

Sub test()    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual


    Dim Rank As Integer
    Dim lstrw As Integer
        
    lstrw = Range("A" & Rows.Count).End(xlUp).Row
    Rank = 1
    
    Range("F2:F" & lstrw).Value = Range("A2:A" & lstrw).Value
    
    For x = 2 To lstrw


    If Cells(x, 5).Value = "" And Cells(x, 3).Value > 0 And Cells(x, 6).Value + Cells(x, 3).Value = Cells(x, 4).Value Or Cells(x, 2).Value > 0 And Cells(x, 6).Value - Cells(x, 2).Value = Cells(x, 4).Value Then
    
        Cells(x, 5).Value = Rank
        Range("F2:F" & lstrw).Value = Cells(x, 4).Value
        
        Rank = Rank + 1
        
        x = 1
        
    End If
    
    Next x
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
        
End Sub

Hopefully, this gets you on the right track.

Best Regards,
Mart van Gestel