Forum Discussion
Formula Help!!
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
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 😞
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 |