Ideas on how to group ledger data

[EugeneNovikov]

Hi all, I'm having a credit/debit transaction ledger and I'm real stuck here. Each record has:

1. Date, connected to a calendar table's Date field as appropriate

2. Time

3. Timestamp but it only has precision of a second (not on the image attached but I have it)

4. Account number, conected to appropriate dimension table, which in this puzzle is only used to filter the ledger 

5. Operation amount (credit/debit)

6. Balance after operation amount was applied to the account

I also have a number of visuals:

I. filtered by Date (date)/Month & Year (text sorted by MonthnYear number)/Year (number) hierarchy build upon calendar table such as matrix and waterfall chart

II. also Account number slicer filtering ledger from Account dimension table,

III. also time period 'between' slicer which can drill down/up through the above time hierarchy.

Now, there could be transactions recorded for same or different accounts with identical timestamps so it's impossible to figure out their real life order, which is fine within this task to have since one day is my lowest granularity for the visuals. There could be dates with no trasactions, or dates with transaction(-s) for one or more account(-s). Calendar dates cover only the period between the earliest and the latest dates in Ledger. 

I need a way to build a measure returning a sum of the amounts in the Balance field for all accounts numbers in scope which are either the latest prior to the start date selected by a slicer or earliest on that date.

These amounts might happen to be

1. Taken from records with different dates

2. On the same date or even bear the same timestamp

3. Missing (then the implication is the balance was zero), or before, or on the earliest date selected by the slicer

I could pick the right balance for a single account by introducing an index column throughout the whole ledger sorted by timestams that would mimic the real unique timestamp, which I don't have. But it won't work for more than one account to get that sum of balances. 

I am now in doubt I can make use of that balance after transaction column at all and the approach of choice would then be to calculate the running sum of operation amounts up to the earliest slicer date upon all the history of every account to get a sum of current accounts' operations for current balances. Which is CPU intense and therefore not good. 

Any thoughts or ideas? Thank you. 

[v-zhouwen-msft]

Hi @EugeneNovikov ,

Regarding your question, you mentioned creating an index column. My idea is to use the 'Summarize' function to create a dummy table based on 'Account number' and get the latest account balance based on the maximum index value for each 'Account number'. Finally use the 'Sumx' function to find the total balance.

Something like this:

Measure = 
VAR _table = SUMMARIZE('Table',[Account number],"Result",MAXX(FILTER('Table',[Index] = MAX('Table'[index])),[balance]))
RETURN SUMX(_table,[Result])

But if you can, please provide simple data and present the expected results as a picture so that it is easier for us to understand.

Best Regards