Forum Discussion
Projection Based off Calculated Field
I am doing five year projections my 2023 data is based off data that already exists to where my formula is the following: 2023 Estimate: = CALCULATE( SUM('GL298A'[BUDGET-DTL] ),'GL298A'[FISCAL...
That's why I put the Xxx2023 variables inside the measure and then referenced them from the Xxx2024 variables. Change the -3s I had to -1s and it should be close to what you're after.
You'll probably have an easier time if you break things into variables.
2024 Estimate =
VAR Personnel2022 =
CALCULATE (
SUM ( 'GL298A'[BUDGET-DTL] ),
'GL298A'[FISCAL-YEAR] = 2022,
'Accounts'[Object Category] = "Personnel"
)
VAR Contractual2022 =
CALCULATE (
SUM ( 'GL298A'[BUDGET-DTL] ),
'GL298A'[FISCAL-YEAR] = 2022,
'Accounts'[Object Category] = "Contractual"
)
VAR Commodities2022 =
CALCULATE (
SUM ( 'GL298A'[BUDGET-DTL] ),
'GL298A'[FISCAL-YEAR] = 2022,
'Accounts'[Object Category] = "Commodities"
)
VAR CapitalOutlay2022 =
CALCULATE (
SUM ( 'GL298A'[BUDGET-DTL] ),
'GL298A'[FISCAL-YEAR] = 2022,
'Accounts'[Object Category] = "Capital Outlay"
)
VAR Debt2022 =
CALCULATE (
SUM ( 'GL298A'[BUDGET-DTL] ),
'GL298A'[FISCAL-YEAR] = 2022,
'Accounts'[Object Category] = "Debt"
)
VAR Personnel2023 = -2 * Personnel2022
VAR Contractual2023 = -2 * Contractual2022
VAR Commodities2023 = -2 * Commodities2022
VAR CapitalOutlay2023 = -2 * CapitalOutlay2022
VAR Debt2023 = .5 * Debt2022
VAR Personnel2024 = -3 * Personnel2023
VAR Contractual2024 = -3 * Contractual2023
VAR Commodities2024 = -3 * Commodities2023
VAR CapitalOutlay2024 = -3 * CapitalOutlay2023
VAR Debt2024 = .4 * Debt2023
RETURN
Personnel2024 + Contractual2024 + Commodities2024 + CapitalOutlay2024 + Debt2024
Okay this makes sense and it is much cleaner and easier, but I need one more step. Based off using your formula above I need another calculation on the sidebar. For example I would use:
2024 Estimate =
VAR Personnel2024 = -1 * 'Yearly Budget'[2023 Estimate:]
VAR Contractual2024 = -1 * 'Yearly Budget'[2023 Estimate:]
VAR Commodities2024 = -1 * 'Yearly Budget'[2023 Estimate:]
VAR CapitalOutlay2024 = -1 * 'Yearly Budget'[2023 Estimate:]
VAR Debt2024 = -1 * 'Yearly Budget'[2023 Estimate:] VAR OtherPayments2024 = -1 * 'Yearly Budget'[2023 Estimate:]
RETURN
Personnel2024 + Contractual2024 + Commodities2024 + CapitalOutlay2024 + Debt2024 +OtherPayments2024 (and so on for following years)
However, when I do this the "yearly budget[2023 Estimate:] is not filtering off the codes such as debt contractual, etc. Is it possible to write the second DAX with filtering by those expense types???
That's why I put the Xxx2023 variables inside the measure and then referenced them from the Xxx2024 variables. Change the -3s I had to -1s and it should be close to what you're after.