Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have looked at many different community posts but I haven't found one that's solution works with my problem.
I am trying to create a running total for a dataset that contains multiple people, with 2 different types of accounts. Most of the time people will have entries for both accounts, however they may sometimes be missing entires for one or both of the types for a month. I have tried solutions in both DAX and Power Query and I have no further ideas.
Here is an example of my data:
Person | Month | Amount | Type |
Jim | 1 | 0 | Receivables |
Jim | 2 | 350 | Receivables |
Jim | 3 | -650 | Prepaid Rent |
Jim | 3 | 275 | Receivables |
Jim | 4 | -5 | Prepaid Rent |
Jim | 4 | 0 | Receivables |
Jim | 5 | -5 | Prepaid Rent |
Jim | 5 | 0 | Receivables |
Jim | 6 | -5 | Prepaid Rent |
Jim | 6 | 0 | Receivables |
Jim | 7 | -5 | Prepaid Rent |
Jim | 7 | 0 | Receivables |
Jim | 8 | -5 | Prepaid Rent |
Jim | 8 | 0 | Receivables |
Jim | 9 | -5 | Prepaid Rent |
Jim | 9 | 0 | Receivables |
Jim | 10 | 700 | Prepaid Rent |
Jim | 10 | -300 | Receivables |
Jim | 11 | -1000 | Receivables |
Fred | 1 | 770 | Receivables |
Fred | 2 | 5000 | Receivables |
Fred | 3 | -7000 | Receivables |
Fred | 4 | -70 | Receivables |
Fred | 5 | -70 | Receivables |
Fred | 6 | -70 | Receivables |
Fred | 7 | -80 | Receivables |
Fred | 8 | -60 | Receivables |
Fred | 9 | -90 | Receivables |
Fred | 10 | -150 | Receivables |
Fred | 11 | 40 | Receivables |
Here is what I want my output to be:
Person | Month | Amount | Type | Running Total |
Jim | 1 | 0 | Receivables | 0 |
Jim | 2 | 350 | Receivables | 350 |
Jim | 3 | -650 | Prepaid Rent | -650 |
Jim | 3 | 275 | Receivables | 625 |
Jim | 4 | -5 | Prepaid Rent | -645 |
Jim | 4 | 0 | Receivables | 625 |
Jim | 5 | -5 | Prepaid Rent | -640 |
Jim | 5 | 0 | Receivables | 625 |
Jim | 6 | -5 | Prepaid Rent | -635 |
Jim | 6 | 0 | Receivables | 625 |
Jim | 7 | -5 | Prepaid Rent | -630 |
Jim | 7 | 0 | Receivables | 625 |
Jim | 8 | -5 | Prepaid Rent | -625 |
Jim | 8 | 0 | Receivables | 625 |
Jim | 9 | -5 | Prepaid Rent | -620 |
Jim | 9 | 0 | Receivables | 625 |
Jim | 10 | 700 | Prepaid Rent | 80 |
Jim | 10 | -300 | Receivables | 325 |
Jim | 11 | -1000 | Receivables | -675 |
Fred | 1 | 770 | Receivables | 770 |
Fred | 2 | 5000 | Receivables | 5770 |
Fred | 3 | -7000 | Receivables | -1230 |
Fred | 4 | -70 | Receivables | -1300 |
Fred | 5 | -70 | Receivables | -1370 |
Fred | 6 | -70 | Receivables | -1440 |
Fred | 7 | -80 | Receivables | -1520 |
Fred | 8 | -60 | Receivables | -1580 |
Fred | 9 | -90 | Receivables | -1670 |
Fred | 10 | -150 | Receivables | -1820 |
Fred | 11 | 40 | Receivables | -1780 |
Each account for each person should have their own running total. So Jim would have one running total for Recievables and another for Prepaid Rent. Fred would have his own unique running totals. There are hundreds of thousands of entries in the real dataset.
Any help would be much appreciated!
Solved! Go to Solution.
@Anonymous ,
Try this measure:
_Running Total = CALCULATE(SUM('Table'[Amount]), FILTER(ALL('Table'[Month]), 'Table'[Month] <= MAX('Table'[Month])))
Thank you!!!!!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |