Forum Discussion
Rolling Deductions
Hi all,
I'm trying to make a table visual like the below. I have a measure within the dataset that is calculating the sum of our deposits from an unrelated table. This table has no dates or other related data so I must work using the total only.
Let's say the total deposit value being calculated as 400,000. Within my dataset there is a colum with deposit values per row that I could use. However this deposit information is not as accurate at the total of 400,000 being calculated from the measure.
Column 1 and 2 is info being pulled from the data table, pretty straight forward. Column 3 however I would like to make it a rolling calculation of how much deposit is remains on the account.
December = Total Deposit (the measure) - column 2 = column 3
January = December's column 3 - January's column 2 = column 3
Feb = January's column 3 - Feb's column 2 = column 3
and so on...
| Column 1 | Column 2 | Column 3 | |
| Month | Sum of Deposit | Deposit Left | |
| December | £ 100,000.00 | £ 300,000.00 | |
| January | £ 20,000.00 | £ 280,000.00 | |
| February | £ 60,000.00 | £ 220,000.00 | |
| March | £ 20,000.00 | £ 200,000.00 | |
| April | £ 10,000.00 | £ 190,000.00 | |
| May | £ - | £ 190,000.00 | |
| June | £ 40,000.00 | £ 150,000.00 | |
| July | £ 50,000.00 | £ 100,000.00 | |
| August | £ 60,000.00 | £ 40,000.00 | |
| September | £ 20,000.00 | £ 20,000.00 | |
| October | £ - | £ 20,000.00 | |
| November | £ 20,000.00 | £ - | |
| £ 400,000.00 |
Is this possible? I've been trying to figure a way but feel this is is way behond my limited knowledge.
The datasources are very small and it will only be used by me, so i don't care if it's clunky. Also our financial year starts in December, I thought I was on the right track but having December as the first month was causing more issues.
Hi ChrisM32 ,
If you want to calculate the rolling results, I think there should be a column like [DateTime]/[Index] in your table to make clear the temporal order of your data.
If you data looks like as your sample, you can add an [Index] column in Power Query Editor.
Calculated Column:
Deposit Left = VAR _Total = CALCULATE(SUM('Table'[Deposit]),ALL('Table')) VAR _ROLLING = CALCULATE(SUM('Table'[Deposit]),FILTER('Table','Table'[Index]<=EARLIER('Table'[Index]))) RETURN _Total - _ROLLINGResult is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi ChrisM32 ,
If you want to calculate the rolling results, I think there should be a column like [DateTime]/[Index] in your table to make clear the temporal order of your data.
If you data looks like as your sample, you can add an [Index] column in Power Query Editor.
Calculated Column:
Deposit Left = VAR _Total = CALCULATE(SUM('Table'[Deposit]),ALL('Table')) VAR _ROLLING = CALCULATE(SUM('Table'[Deposit]),FILTER('Table','Table'[Index]<=EARLIER('Table'[Index]))) RETURN _Total - _ROLLINGResult is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
So strangely enough I created a table pretty much indentical to yours as I figured an index number would be the best way but I could not for the life of me figure out how to do the rolling calculation.
You are aabsoloutely amazing, thank you!
ChrisM32 , This seems like inventory calculation needs to build from start using date table
example
Inventory / OnHand
[Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw
