Formula Help!!

[ChloeDL]

Basically, we have this huge dataset with individual line items for every transaction which includes prj info, budget, fiscal year etc. First, we need a formula to get the cumulative spent by budget by year. Second we need a formula that will subtract the actuals from given total budget amount again by budget and fiscal. Thing is the dataset doesn’t have budget amounts. It has what was allocated and what was spent where we need to show when the “bucket” will run out of money. Here's an example of what we're trying to accoplish. 

	2018-2019			2019-2020			2020-2021
	Actuals	Cumul.	Left	Actual	Cumul.	Left	Actual	Cumul.	Left
B2018	6		10-6=4	6	12	10-12=-(2)	6	18	10-18=-(8)
X	1		10-1=9	1	1	10-2=8	1	3	10-3=7
Y	2		10-2=8	2	4	10-4=6	2	6	10-6=4
Z	3		10-3=7	3	6	10-6=4	3	9	10-9=1
B2021	6		20-6=14	6	12	20-12=8	6	18	20-18=2
X	1		20-1=19	1	2	20-2=18	1	3	20-3=17
Y	2		20-2=18	2	4	20-4=16	2	6	20-6=14
Z	3		20-3=17	3	6	20-6=14	3	9	20-9=11

[Anonymous · Accepted solution]

Hi  , @ChloeDL 

Is this result you want to achieve?

you just need to create two new columns:

sum = CALCULATE(SUM('Table'[actuals]),FILTER(ALL('Table'),'Table'[actuals]<=EARLIER('Table'[actuals])))

value = 'Table'[TBbudget]-'Table'[sum]

This is my testing PBIX  file.

Best regards,

Community Support Team Selina zhu

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

[daXtreme]

Hi @ChloeDL 

It's good you show what you want to accomplish, we appreciate that... but where the piece where you show your data and model?

[ChloeDL]

Thanks for your response. Unfortunately, I work for GCC and cannot give you a screnshot of the data but I'm hoping this helps. Here's the formula we currently use for the cumulative spend. Which works well but doesn't seperate by "bucket of money".  We're using the matrix visuals to show each projects within each budget "bucket" and fiscal years either as columns or as a slicer. 

Cumulative spent = CALCULATE(SUM(Append1[Actual Expenditures]),ALL('Append1'),Append1[FiscalYear] <= MAX('Append1'[FiscalYear]))

Here's a screnshot of the relationships made, all of our data is within the Append1 table. 

Here's a copy of our data columns, highlighted are the "used" columns for this requirement. 

[daXtreme]

Hi there.

---

@ChloeDL wrote:

Basically, we have this huge dataset with individual line items for every transaction which includes prj info, budget, fiscal year etc. First, we need a formula to get the cumulative spent by budget by year. Second we need a formula that will subtract the actuals from given total budget amount again by budget and fiscal. Thing is the dataset doesn’t have budget amounts.

I don't quite get this. In one sentence you say there's budget in the table, in another you say you don't have the budget amounts... Not sure if I can help here 😞

[ChloeDL]

Sorry for not being clear. The budget colum shows the bucket from which each actuals is coming out of (b2018 or b2021). I've tested things and added the TBBudget column which shows the total budget amount but I need it to subtract each actuals 

Currently it shows 

Budget	Actuals	TBBudget
B2018	$10	$100
B2018	$20	$100
B2018	$30	$100
B2021	$40	$200
B2021	$50	$200

Where we need it to show 

Budget	Actuals	TBBudget
B2018	10	100-10=90
B2018	20	100-30=70
B2018	30	100-60=40
B2021	40	200-40=160
B2021	50	200-90=110