Rolling Deductions

[ChrisM32]

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. 

[v-rzhou-msft · Accepted solution]

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 - _ROLLING

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ChrisM32]

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!

[amitchandak]

@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