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